[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct package_id,
netting_trx_type_id
FROM igi_stp_candidates_all
WHERE batch_id =p_batch_id
AND org_id = p_org_id;
SELECT sum(amount) ap_amount
FROM igi_stp_candidates
WHERE batch_id = p_batch_id
AND application = 'AP'
AND netting_trx_type_id = p_netting_trx_type_id
AND package_id = p_package_id;
SELECT sum(amount) ar_amount
FROM igi_stp_candidates_ALL
WHERE batch_id = p_batch_id
AND application = 'AR'
AND ORG_ID = P_ORG_ID
AND netting_trx_type_id = p_netting_trx_type_id
AND package_id = p_package_id;
SELECT sum(amount) ap_amount
FROM igi_stp_candidates cand,
igi_stp_net_type_alloc net
WHERE cand.batch_id = p_batch_id
AND cand.application = 'AP'
AND cand.netting_trx_type_id = p_netting_trx_type_id
AND cand.package_id = p_package_id
AND net.netting_trx_type_id = cand.netting_trx_type_id
AND net.application = decode(cand.application,'AP','SQLAP',cand.application)
AND net.trx_type_class = p_trx_type_class;
SELECT package_num,
application,
trx_id,
trx_number,
stp_id,
site_id,
reference,
(amount - netting_amount) Amount, -- Bug 2938450 (Details of changes on top)
currency_code,
exchange_rate,
exchange_rate_type,
exchange_date
FROM igi_stp_candidates
WHERE batch_id = p_batch_id
AND package_id = p_package_id
AND netting_trx_type_id = p_netting_trx_type_id
AND process_flag = 'S'
AND application = 'AR';
SELECT package_num,
application,
trx_id,
trx_number,
stp_id,
site_id,
reference,
(amount - netting_amount) Amount, -- Bug 2938450 (Details of changes on top)
currency_code,
exchange_rate,
exchange_rate_type,
exchange_date
FROM igi_stp_candidates
WHERE batch_id = p_batch_id
AND package_id = p_package_id
AND netting_trx_type_id = p_netting_trx_type_id
AND process_flag = 'S'
AND application = 'AP';
SELECT cand.package_num,
cand.application,
cand.trx_id,
cand.trx_number,
cand.stp_id,
cand.site_id,
cand.reference,
(cand.amount - cand.netting_amount) Amount, -- Bug 2938450 (Details of changes on top)
net.trx_type_class,
currency_code,
exchange_rate,
exchange_rate_type,
exchange_date
FROM igi_stp_candidates_all cand,
igi_stp_net_type_alloc_all net
WHERE cand.batch_id = p_batch_id
AND cand.package_id = p_package_id
AND cand.netting_trx_type_id = p_netting_trx_type_id
AND cand.process_flag = 'S'
AND cand.application = 'AP'
AND net.netting_trx_type_id = cand.netting_trx_type_id
AND net.application = decode(cand.application,'AP','SQLAP',cand.application)
AND net.trx_type_class = 'INV'
and cand.org_id = p_org_id
and cand.org_id = net.org_id;
select vendor_id from igi_po_vendors
where customer_id = p_customer_id;
select vendor_site_id , accts_pay_code_combination_id
from ap_supplier_sites_all
where vendor_id = p_vendor_id
and pay_site_flag = 'Y'
and org_id = p_org_id
and rownum = 1;
select customer_id from igi_po_vendors
where vendor_id = p_vendor_id;
select CSU.site_use_id, gl_id_rec
from HZ_PARTY_SITES PS,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL CAS,
HZ_CUST_SITE_USES CSU
where CAS.party_site_id = PS.party_site_id
AND LOC.location_id = PS.location_id
and CAS.CUST_ACCOUNT_ID = p_customer_id
and CSU.cust_acct_site_id = CAS.cust_acct_site_id
and CSU.site_use_code ='BILL_TO'
AND CSU.PRIMARY_FLAG ='Y';
select CSU.site_use_id
from HZ_PARTY_SITES PS,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL CAS,
HZ_CUST_SITE_USES CSU
where CAS.party_site_id = PS.party_site_id
AND LOC.location_id = PS.location_id
and CAS.CUST_ACCOUNT_ID = l_stp_id
and CSU.cust_acct_site_id = CAS.cust_acct_site_id
and CSU.site_use_code ='BILL_TO'
AND CSU.PRIMARY_FLAG ='Y';
select vendor_site_id
from ap_supplier_sites_all
where vendor_id = l_stp_id
and pay_site_flag ='Y'
and vendor_site_id = l_site_id
and org_id = p_org_id;
select invoice_currency_code
into l_currency_code
from ap_invoices
where invoice_num = l_trx_number;
select invoice_currency_code
into l_currency_code
from ra_customer_trx
where trx_number = l_trx_number;
l_message := 'Insert into the packages table';
INSERT INTO igi_stp_packages_all
( BATCH_ID ,
PACKAGE_ID ,
PACKAGE_NUM ,
ORG_ID ,
STP_ID ,
SITE_ID ,
APPLICATION ,
AMOUNT ,
ACCOUNTING_DATE ,
TRX_NUMBER ,
RELATED_TRX_NUMBER ,
REFERENCE ,
NETTING_TRX_TYPE_ID ,
TECHNICAL_CCID ,
REC_OR_LIAB_CCID ,
TRX_TYPE_CLASS ,
DOC_CATEGORY_CODE ,
DESCRIPTION ,
CURRENCY_CODE ,
EXCHANGE_RATE ,
EXCHANGE_RATE_TYPE ,
EXCHANGE_DATE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE )
SELECT
p_batch_id ,
l_package_id ,
l_package_num ,
P_ORG_ID ,
l_stp_id ,
nvl(local_site_Id,l_site_id) ,
l_application ,
l_amount ,
sysdate ,
l_prefix||to_char(igi_stp_trx_s.nextval) ,
l_trx_number ,
l_reference ,
l_netting_trx_type_id ,
net.netting_expense_ccid ,
l_ccid ,
l_trx_type_class ,
net.doc_category_code ,
net.netting_trx_type_id ,
l_currency_code ,
l_exchange_rate ,
l_exchange_rate_type ,
l_exchange_date ,
-1 ,
sysdate ,
-1 ,
sysdate
FROM igi_stp_net_type_alloc_ALL net
WHERE net.netting_trx_type_id = l_netting_trx_type_id
AND net.application = decode(l_application,'AP', 'SQLAP',l_application)
AND net.trx_type_class = l_trx_type_class
AND ORG_ID = P_ORG_ID;
PROCEDURE Delete_Candidates (p_user_id in number)
IS
BEGIN
-- Delete Candidates --
--ssemwal for Bug 2437020 included where condition
DELETE FROM igi_stp_candidates
WHERE user_id = p_user_id ;
FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Delete_Candidates',TRUE);
RAISE_APPLICATION_ERROR(-20010, 'Error in delete_candidates procedure');
END Delete_Candidates;
-- Bug 2938450 (Tpradhan), Replaced the from dual select statement with direct assignment
l_ar_netting_amount := least(nvl(l_get_ap_amount,0),nvl(l_get_ar_amount,0));
SELECT dist.code_combination_id
INTO l_rec_ccid
FROM ra_customer_trx trx,
ra_cust_trx_line_gl_dist dist,
igi_stp_candidates candidates
WHERE dist.customer_trx_id = trx.customer_trx_id
AND dist.account_class = 'REC'
AND trx.trx_number = candidates.trx_number
AND candidates.package_id = p_package_id
AND candidates.netting_trx_type_id = p_netting_trx_type_id
AND candidates.batch_id = p_batch_id
AND rownum = 1;
SELECT api.accts_pay_code_combination_id
INTO l_liability_ccid
FROM ap_invoices api,
igi_stp_candidates candidates
WHERE api.invoice_id = candidates.trx_id
AND candidates.package_id = p_package_id
AND candidates.netting_trx_type_id = p_netting_trx_type_id
AND candidates.batch_id = p_batch_id
AND rownum = 1;
SELECT vendor_site_id
INTO l_contra_party_site_id
FROM ap_supplier_sites_all
WHERE vendor_id = p_contra_party_id
AND org_id = p_org_id
AND rownum = 1;
SELECT api.accts_pay_code_combination_id
INTO l_liability_ccid
FROM ap_invoices_all api,
igi_stp_candidates_all candidates
WHERE api.invoice_id = candidates.trx_id
AND candidates.package_id = p_package_id
AND candidates.netting_trx_type_id = p_netting_trx_type_id
AND candidates.batch_id = p_batch_id
and candidates.org_id = p_org_id
and api.org_id = candidates.org_id
AND rownum = 1;
Delete_Candidates (l_user_id);
SELECT dist.code_combination_id
INTO l_rec_ccid
FROM ra_customer_trx trx,
ra_cust_trx_line_gl_dist dist,
igi_stp_candidates candidates
WHERE dist.customer_trx_id = trx.customer_trx_id
AND dist.account_class = 'REC'
AND trx.trx_number = candidates.trx_number
AND candidates.package_id = p_package_id
AND candidates.netting_trx_type_id = p_netting_trx_type_id
AND candidates.batch_id = p_batch_id
AND rownum = 1;
SELECT api.accts_pay_code_combination_id
INTO l_liability_ccid
FROM ap_invoices api,
igi_stp_candidates candidates
WHERE api.invoice_id = candidates.trx_id
AND candidates.package_id = p_package_id
AND candidates.netting_trx_type_id = p_netting_trx_type_id
AND candidates.batch_id = p_batch_id
AND rownum = 1;