[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pck.package_id,
net.cust_trx_type_id,
pck.rec_or_liab_ccid,
pck.technical_ccid,
pck.stp_id,
pck.site_id,
rsu.cust_acct_site_id,
pck.amount,
pck.description,
pck.trx_number,
pck.trx_type_class,
pck.doc_category_code,
pck.related_trx_number,
pck.accounting_date,
pck.currency_code,
c.batch_id,
pck.exchange_rate,
pck.exchange_rate_type,
pck.exchange_date
FROM igi_stp_packages_all pck,
igi_stp_control c,
HZ_CUST_SITE_USES rsu,
igi_stp_net_type_alloc_all net
WHERE c.control_id = p_net_batch_id
AND pck.batch_id = c.batch_id
AND pck.application ='AR'
AND rsu.site_use_id = pck.site_id
AND net.netting_trx_type_id = pck.netting_trx_type_id
AND net.trx_type_class = pck.trx_type_class
AND net.application = pck.application
and pck.org_id = p_org_id
and pck.org_id = net.org_id;
l_message := 'Insert a new AR document';
SELECT batch_source_id,
name
INTO l_batch_source_id,
l_batch_source_name
FROM ra_batch_sources_all
WHERE name = p_receivables_batch_source
and org_id = p_org_id;
/* select fpov.profile_option_value
into l_term_id
from fnd_profile_option_values fpov,
fnd_profile_options fpo
where fpo.profile_option_id = fpov.profile_option_id
and profile_option_name = 'IGI_STP_AR_TERMS'; */
/* select fpov.profile_option_value
into l_uom_code
from fnd_profile_option_values fpov,
fnd_profile_options fpo
where fpo.profile_option_id = fpov.profile_option_id
and profile_option_name = 'IGI_STP_UOM'; */
INSERT INTO ra_interface_lines_ALL( amount
, batch_source_name -- Mandatory
, comments
, description -- Mandatory
, currency_code -- Mandatory
, gl_date
, conversion_date
, conversion_rate
, conversion_type -- Mandatory
, cust_trx_type_id
, interface_line_attribute1
, interface_line_attribute2
, interface_line_attribute3
, interface_line_attribute4
, interface_line_attribute5
, interface_line_attribute6
, interface_line_attribute7
, interface_line_context
, link_to_line_context
, line_number
, line_type -- Mandatory
, orig_system_bill_customer_id
, orig_system_bill_address_id
, set_of_books_id -- Mandatory
-- , document_number
, trx_number
, uom_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, term_id
,ORG_ID)
VALUES ( round(ar_rec.amount,2)
, l_batch_source_name
, l_batch_source_name||' '||ar_rec.trx_number
, ar_rec.description
, nvl(ar_rec.currency_code,p_currency_code)
, ar_rec.accounting_date
-- , p_sysdate
-- , 1
-- , 'User'
, nvl(ar_rec.exchange_date,sysdate)
, nvl(ar_rec.exchange_rate,1)
, nvl(ar_rec.exchange_rate_type,'User')
, ar_rec.cust_trx_type_id
, ar_rec.stp_id
, ar_rec.site_id
, to_char(ar_rec.batch_id)
, ar_rec.package_id
, ar_rec.trx_number
, ar_rec.trx_type_class
, ar_rec.related_trx_number
, p_interface_context
, p_interface_context
, l_standing_charge_count
, l_line_type
, ar_rec.stp_id
, ar_rec.cust_acct_site_id
, p_set_of_books_id
-- , l_doc_sequence_number
, ar_rec.trx_number
, l_uom_code
, p_user_id
, p_sysdate
, p_user_id
, p_sysdate
, p_login_id
, decode(ar_rec.trx_type_class,'CM','',l_term_id)
,P_ORG_ID
);
l_message := 'Inserting receivable distribution for '||ar_rec.trx_number;
INSERT INTO ra_interface_distributions_ALL( account_class -- Mandatory
, interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, interface_line_attribute3
, interface_line_attribute4
, interface_line_attribute5
, interface_line_attribute6
, interface_line_attribute7
, percent
, code_combination_id
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
,ORG_ID
)
VALUES ( 'REC'
, p_interface_context
, ar_rec.stp_id
, ar_rec.site_id
, to_char(ar_rec.batch_id)
, ar_rec.package_id
, ar_rec.trx_number
, ar_rec.trx_type_class
, ar_rec.related_trx_number
, 100
, l_receivable_cc_id
, p_user_id
, p_sysdate
, p_user_id
, p_sysdate
, p_login_id
, p_org_id
);
l_message := 'Inserting revenue distribution for '||ar_rec.trx_number;
INSERT INTO ra_interface_distributions_ALL( account_class -- Mandatory
, interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, interface_line_attribute3
, interface_line_attribute4
, interface_line_attribute5
, interface_line_attribute6
, interface_line_attribute7
, percent
, code_combination_id
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, org_id
)
VALUES ( 'REV'
, p_interface_context
, ar_rec.stp_id
, ar_rec.site_id
, to_char(ar_rec.batch_id)
, ar_rec.package_id
, ar_rec.trx_number
, ar_rec.trx_type_class
, ar_rec.related_trx_number
, 100
, l_revenue_cc_id
, p_user_id
, p_sysdate
, p_user_id
, p_sysdate
, p_login_id
, p_org_id
);
UPDATE igi_stp_batches
SET batch_status = 'ARFAILED'
WHERE batch_id in
(select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'ARFAILED'
WHERE batch_id in
(select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
SELECT pck.package_id,
pck.rec_or_liab_ccid,
pck.technical_ccid,
pck.stp_id,
pck.site_id,
pck.amount,
pck.description,
pck.trx_number,
pck.trx_type_class,
type.cust_trx_type_id invoice_type_lookup_code,
pck.accounting_date,
pck.doc_category_code,
pck.currency_code,
pck.exchange_rate,
pck.exchange_rate_type,
pck.exchange_date
FROM igi_stp_packages_all pck,
igi_stp_control c,
igi_stp_net_type_alloc_all type
WHERE c.control_id = p_net_batch_id
AND pck.batch_id = c.batch_id
AND pck.application = 'AP'
AND type.netting_trx_type_id = pck.netting_trx_type_id
AND type.trx_type_class = pck.trx_type_class
AND type.application = 'SQLAP'
and pck.org_id = p_org_id
and pck.org_id = type.org_id;
select lookup_code
into l_ap_source
-- from IGI_AP_PO_LOOKUP_CODES_V
from AP_LOOKUP_CODES
where lookup_type = 'SOURCE'
and lookup_code = p_payables_batch_source;
/* select fpov.profile_option_value
into l_term_id
from fnd_profile_option_values fpov,
fnd_profile_options fpo
where fpo.profile_option_id = fpov.profile_option_id
and profile_option_name = 'IGI_STP_AP_TERMS'; */
select ap_invoices_s.nextval
into l_invoice_id
from dual;
l_message := 'Inserting line for '||ap_rec.trx_number;
insert into ap_invoices_interface
(ACCTS_PAY_CODE_COMBINATION_ID,
CREATED_BY,
CREATION_DATE,
DESCRIPTION,
DOC_CATEGORY_CODE,
GL_DATE,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
INVOICE_DATE,
INVOICE_ID,
INVOICE_NUM,
INVOICE_TYPE_LOOKUP_CODE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ORG_ID,
SOURCE,
STATUS,
TERMS_ID,
VENDOR_ID,
VENDOR_SITE_ID,
PAY_GROUP_LOOKUP_CODE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
INVOICE_RECEIVED_DATE) -- bug6847252
values
(ap_rec.rec_or_liab_ccid, -- ACCTS_PAY_CODE_COMBINATION_ID
p_user_id, -- CREATED_BY
p_sysdate, -- CREATION_DATE
'', -- DESCRIPTION
ap_rec.doc_category_code, -- DOC_CATEGORY_CODE
p_sysdate, -- GL_DATE
round(ap_rec.amount,2), -- INVOICE_AMOUNT
nvl(ap_rec.currency_code,
p_currency_code), -- INVOICE_CURRENCY_CODE
p_sysdate, -- INVOICE_DATE
l_invoice_id, -- INVOICE_ID
ap_rec.trx_number, -- INVOICE_NUM
ap_rec.invoice_type_lookup_code, -- INVOICE_TYPE_LOOKUP_CODE
p_user_id, -- LAST_UPDATED_BY
p_sysdate, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATE_LOGIN
p_org_id, -- ORG_ID
l_ap_source, -- SOURCE
'', -- STATUS
l_term_id, -- TERMS_ID
ap_rec.stp_id, -- VENDOR_ID
ap_rec.site_id, -- VENDOR_SITE_ID
l_pay_group,
ap_rec.exchange_rate,
ap_rec.exchange_rate_type,
ap_rec.exchange_date,
p_sysdate); -- bug6847252
l_message := 'Inserting distribution for '||ap_rec.trx_number;
insert into ap_invoice_lines_interface
(ACCOUNTING_DATE,
AMOUNT,
CREATED_BY,
CREATION_DATE,
DESCRIPTION,
DIST_CODE_COMBINATION_ID,
INVOICE_ID,
INVOICE_LINE_ID,
ITEM_DESCRIPTION,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
ORG_ID)
values
(p_sysdate, -- ACCOUNTING_DATE
round(ap_rec.amount,2), -- AMOUNT
p_user_id, -- CREATED_BY
p_sysdate, -- CREATION_DATE
'', -- DESCRIPTION
ap_rec.technical_ccid, -- DIST_CODE_COMBINATION_ID
l_invoice_id, -- INVOICE_ID
ap_invoice_lines_interface_s.nextval,
-- INVOICE_LINE_ID
ap_rec.description, -- ITEM_DESCRIPTION
p_user_id, -- LAST_UPDATED_BY
p_sysdate, -- LAST_UPDATE_DATE
p_login_id, -- LAST_UPDATE_LOGIN
1, -- LINE_NUMBER
'ITEM', -- LINE_TYPE_LOOKUP_CODE
p_org_id -- ORG_ID
);
UPDATE igi_stp_batches
SET batch_status = 'APFAILED'
WHERE batch_id in
(select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'APFAILED'
WHERE batch_id in
(select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
select currency_code
into p_currency_code
from gl_ledgers_public_v
where ledger_id = p_set_of_books_id;
UPDATE igi_stp_batches
SET batch_status = 'APFAILED'
WHERE batch_id in (select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'APFAILED'
WHERE batch_id in (select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
SELECT netting_trx_type_id
FROM igi_stp_batches_all
WHERE batch_id in
(select batch_id
from igi_stp_control
where control_id =p_net_batch_id)
and org_id = p_org_id;
select chart_of_accounts_id
into l_chart_of_accounts_id
from gl_ledgers_public_v
where ledger_id = p_set_of_books_id;
select currency_code
into p_currency_code
from gl_ledgers_public_v
where ledger_id = p_set_of_books_id;
select lookup_code
into l_ap_source
-- from IGI_AP_PO_LOOKUP_CODES_V
from AP_LOOKUP_CODES
where lookup_type = 'SOURCE'
and lookup_code = p_payables_batch_source;
update ap_expense_report_headers
set vouchno = 0
where vouchno in
(select batch_id
from igi_stp_control
where control_id
= p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'APFAILED'
WHERE batch_id in
(select batch_id
from igi_stp_control
where control_id
= p_net_batch_id);
SELECT batch_source_id
INTO l_batch_source_id
FROM ra_batch_sources_all
WHERE name = l_batch_source_name
and org_id = p_org_id;
UPDATE igi_stp_batches
SET batch_status = 'ARFAILED'
WHERE batch_id in
(select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'COMPLETE'
WHERE batch_id in
(select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'COMPLETE'
WHERE batch_id in
(select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'COMPLETE'
WHERE batch_id in
(select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'APFAILED'
WHERE batch_id in (select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'ARFAILED'
WHERE batch_id in (select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'APFAILED'
WHERE batch_id in (select batch_id
from igi_stp_control
where control_id = p_net_batch_id);
UPDATE igi_stp_batches
SET batch_status = 'ARFAILED'
WHERE batch_id in (select batch_id
from igi_stp_control
where control_id = p_net_batch_id);