[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_Select_Clause VARCHAR2(5000):= '
SELECT
pi.ORG_ID as org_id,
pi.payment_item_id,
pi.payment_term_id as payment_term_id,
pi.export_currency_amount,
pi.export_currency_code,
pi.vendor_id,
pi.vendor_site_id as vendor_site_id,
pt.project_id,
pt.task_id,
pt.organization_id,
pt.expenditure_type,
pt.expenditure_item_date,
pt.tax_group_id,
pt.tax_code_id,
pt.tax_classification_code,
pt.tax_included,
pt.distribution_set_id,
le.lease_num,
le.lease_id,
NVL(pld.send_entries, ''Y'') as send_entries,
pi.payment_schedule_id,
ps.period_name,
PNP_UTIL_FUNC.get_start_date(ps.period_name,pn_mo_cache_utils.get_current_org_id) as gl_date,
pt.normalize,
pi.due_date,
pt.ap_ar_term_id,
TRUNC(pi.accounted_date),
pi.rate,
pi.ap_invoice_num,
pt.payment_purpose_code,
pt.payment_term_type_code,
pn_exp_to_ap.get_liability_acc(pi.payment_term_id,
pi.vendor_id,
pi.vendor_site_id) as lia_account,
pt.legal_entity_id as legal_entity_id,
decode(UPPER(PNP_UTIL_FUNC.check_conversion_type('''||l_func_curr_code||''',pn_mo_cache_utils.get_current_org_id))
, ''USER'', decode(pi.export_currency_code
,'''||l_func_curr_code||''', 1
,pi.rate)
, NULL) as conv_rate,
PNP_UTIL_FUNC.check_conversion_type('''||l_func_curr_code||''',pn_mo_cache_utils.get_current_org_id)
as conv_rate_type,
pi.grouping_rule_id as item_grouping_rule_id,
pt.grouping_rule_id as term_grouping_rule_id,
pld.grouping_rule_id as lease_grouping_rule_id
FROM pn_payment_items pi,
pn_payment_schedules_all ps,
pn_payment_terms_all pt,
pn_leases_all le,
pn_lease_details_all pld,
fnd_lookups type_lookup,
fnd_lookups purpose_lookup,
po_vendors vendor,
hr_operating_units ou,
pn_pay_group_rules ppgr
WHERE pi.payment_term_id = pt.payment_term_id
AND pi.payment_schedule_id = ps.payment_schedule_id
AND nvl(pi.export_to_ap_flag,''N'') = ''Y''
AND pi.payment_item_type_lookup_code = ''CASH''
AND pt.lease_id = le.lease_id
AND pld.lease_id = le.lease_id
AND le.parent_lease_id IS NULL
AND pi.transferred_to_ap_flag IS NULL
AND pi.vendor_id IS NOT NULL
AND pi.export_currency_amount <> 0
AND pi.grouping_rule_id = ppgr.grouping_rule_id (+)
AND type_lookup.lookup_type = ''PN_PAYMENT_TERM_TYPE''
AND type_lookup.lookup_code = pt.payment_term_type_code
AND purpose_lookup.lookup_type = ''PN_PAYMENT_PURPOSE_TYPE''
AND purpose_lookup.lookup_code = pt.payment_purpose_code
AND vendor.vendor_id = pi.vendor_id
AND ou.organization_id = pi.org_id
AND 1 = :l_one ';
SELECT account_id,
account_class,
percentage
FROM pn_distributions_all
WHERE payment_term_id = p_term_id;
SELECT name
FROM ap_tax_codes_all
WHERE tax_id = p_tax_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
SELECT grouping_rule_id
FROM pn_system_setup_options
WHERE org_id = p_org_ID;
SELECT group_by_id,
grouping_rule_id,
group_by_lookup_code
FROM pn_pay_group_bys
WHERE grouping_rule_id = p_grouping_rule_id;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
l_last_update_date DATE := sysdate;
SELECT account_id
FROM pn_distributions_all
WHERE payment_term_id = p_term_id
AND account_class = 'LIA';
SELECT site.accts_pay_code_combination_id as accts_pay_code_combination_id
FROM po_vendor_sites site
WHERE site.vendor_site_id = p_vendor_site_id;
SELECT 1
FROM DUAL
WHERE EXISTS(SELECT 1
FROM ap_invoices_all
WHERE invoice_num = p_invoice_num
AND vendor_id = p_vendor_id
AND org_ID = p_org_ID);
SELECT 1
FROM DUAL
WHERE EXISTS(SELECT 1
FROM ap_invoices_interface
WHERE invoice_num = p_invoice_num
AND vendor_id = p_vendor_id
AND org_ID = p_org_ID);
exp_ap_cache.DELETE;
Q_Payitem1 := 'SELECT DISTINCT payment_term_id,
vendor_site_id,
lia_account,
org_id
FROM ( '
|| Q_Payitem ||
' ) WHERE legal_entity_id IS NULL ';
term_ID_tbl.DELETE;
LE_tbl.DELETE;
UPDATE pn_payment_terms_all
SET legal_entity_id = LE_tbl(i)
WHERE payment_term_id = term_ID_tbl(i);
exp_ap_cache.DELETE(l_index);
SELECT invoice_id
,invoice_num
FROM ap_invoices_interface
WHERE invoice_num = p_invoice_num
AND vendor_id = p_vendor_id
AND org_ID = p_org_ID;
inserted_inv_t inv_tab;
SELECT message_name
FROM fnd_new_messages
WHERE application_id = 240
AND message_name = 'PN_CANNOT_GRP_ON_INV';
inserted_inv_t.DELETE;
bad_inv_t.DELETE;
l_context := 'Insert into the header';
l_context := 'Inserting into ap_invoices_interface ...';
/* delete the data for the bad invoice number */
IF inv_rec.invoice_id = inserted_inv_t(inserted_inv_t.LAST).invoice_id AND
inv_rec.invoice_num = inserted_inv_t(inserted_inv_t.LAST).invoice_num
THEN
l_error_ctr := l_error_ctr + inserted_inv_t(inserted_inv_t.LAST).items_proc;
ROLLBACK TO beforeinsert;
SAVEPOINT beforeinsert;
INSERT INTO ap_invoices_interface
(invoice_id
,invoice_num
,invoice_amount
,invoice_currency_code
,description
,source
,vendor_id
,vendor_site_id
,accts_pay_code_combination_id
,last_updated_by
,last_update_date
,last_update_login
,created_by
,creation_date
,org_id
,group_id
,gl_date
,terms_date
,invoice_date
,invoice_received_date
,terms_id
,legal_entity_id
,exchange_rate
,exchange_rate_type
,exchange_date
,CALC_TAX_DURING_IMPORT_FLAG
,ADD_TAX_TO_INV_AMT_FLAG)--For Bug 9068811
VALUES
(AP_INVOICES_INTERFACE_S.nextval
,NVL(l_invoice_num
,'PN-'||PN_PAYMENT_ITEMS_NUM_S.nextval)
,ROUND(l_header_amount,l_precision)
,exp_ap_cache(l_start).pn_export_currency_code
,'Lease Number: ' || exp_ap_cache(l_start).pn_lease_num
,'Oracle Property Manager'
,exp_ap_cache(l_start).pn_vendor_id
,exp_ap_cache(l_start).pn_vendor_site_id
,exp_ap_cache(l_start).pn_lia_account
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_created_by
,l_creation_date
,exp_ap_cache(l_start).org_id
,p_group_id
,exp_ap_cache(l_start).gl_date -- gl_date
,exp_ap_cache(l_start).pn_due_date -- terms date
,exp_ap_cache(l_start).pn_due_date -- invoice date
,exp_ap_cache(l_start).pn_due_date -- invoice received date
,exp_ap_cache(l_start).pn_ap_ar_term_id
,exp_ap_cache(l_start).pn_legal_entity_id
,exp_ap_cache(l_start).conv_rate
,exp_ap_cache(l_start).conv_rate_type
,exp_ap_cache(l_start).pn_accounted_date
,'Y'
,'Y')--For Bug 9068811
RETURNING invoice_id, invoice_num INTO l_invoice_id, l_invoice_num;
IF inserted_inv_t.LAST IS NULL THEN
l_temp_count := 1;
l_temp_count := inserted_inv_t.LAST + 1;
inserted_inv_t(l_temp_count).invoice_id := l_invoice_id;
inserted_inv_t(l_temp_count).invoice_num := l_invoice_num;
inserted_inv_t(l_temp_count).items_proc := l_next - l_start;
lia_acnt_tab.delete;
acc_acnt_tab.delete;
exp_acnt_tab.delete;
l_context := 'Inserting into ap_invoice_lines_interface ...';
INSERT INTO ap_invoice_lines_interface
(invoice_id
,invoice_line_id
,line_type_lookup_code
,amount
,description
,dist_code_combination_id
,DEFAULT_DIST_CCID
,last_updated_by
,last_update_date
,last_update_login
,created_by
,creation_date
,line_number
,org_id
,amount_includes_tax_flag -- Tax Inclusive
,distribution_set_id
,project_id
,task_id
,expenditure_type
,expenditure_item_date
,expenditure_organization_id
,tax_code_id
,tax_code
,tax_classification_code
,tax_code_override_flag)
VALUES
(l_invoice_id
,AP_INVOICE_LINES_INTERFACE_S.nextval
,'ITEM'
,ROUND(((exp_ap_cache(item).pn_export_currency_amount)
* nvl(acc_acnt_tab(i).percentage,100)/100), l_precision)
,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
,acc_acnt_tab(i).account_id
,acc_acnt_tab(i).account_id
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_created_by
,l_creation_date
,l_lineNumber
,exp_ap_cache(item).org_id
,exp_ap_cache(item).pn_tax_included
,exp_ap_cache(item).pn_distribution_set_id
,exp_ap_cache(item).pn_project_id
,exp_ap_cache(item).pn_task_id
,exp_ap_cache(item).pn_expenditure_type
,exp_ap_cache(item).pn_expenditure_item_date
,exp_ap_cache(item).pn_organization_id
,nvl(exp_ap_cache(item).pn_tax_code_id,
exp_ap_cache(item).pn_tax_group_id)
,l_tax_name
,exp_ap_cache(item).pn_tax_classification_code
,l_tax_code_override_flag)
RETURNING invoice_line_id, amount INTO l_invoice_line_id, l_line_amount;
l_context := 'Inserting into ap_invoice_lines_interface ...';
INSERT INTO ap_invoice_lines_interface
( invoice_id
,invoice_line_id
,line_type_lookup_code
,amount
,description
,dist_code_combination_id
,DEFAULT_DIST_CCID
,last_updated_by
,last_update_date
,last_update_login
,created_by
,creation_date
,line_number
,org_id
,amount_includes_tax_flag -- Tax Inclusive
,distribution_set_id
,project_id
,task_id
,expenditure_type
,expenditure_item_date
,expenditure_organization_id
,tax_code_id
,tax_code
,tax_classification_code /*--Bug 6392393--*/
,tax_code_override_flag)
VALUES
(l_invoice_id
,AP_INVOICE_LINES_INTERFACE_S.nextval
,'ITEM'
,l_amt
,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
,exp_acnt_tab(i).account_id
,exp_acnt_tab(i).account_id
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_created_by
,l_creation_date
,l_lineNumber
,exp_ap_cache(item).org_id
,exp_ap_cache(item).pn_tax_included
,exp_ap_cache(item).pn_distribution_set_id
,exp_ap_cache(item).pn_project_id
,exp_ap_cache(item).pn_task_id
,exp_ap_cache(item).pn_expenditure_type
,exp_ap_cache(item).pn_expenditure_item_date
,exp_ap_cache(item).pn_organization_id
,nvl(exp_ap_cache(item).pn_tax_code_id,
exp_ap_cache(item).pn_tax_group_id)
,l_tax_name
,exp_ap_cache(item).pn_tax_classification_code /*--Bug 6392393--*/
,l_tax_code_override_flag)
RETURNING invoice_line_id, amount INTO l_invoice_line_id, l_line_amount;
UPDATE pn_payment_items_all
SET transferred_to_ap_flag = 'Y' ,
ap_invoice_num = l_invoice_num,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date ,
export_group_id = p_group_id
WHERE payment_item_id = exp_ap_cache(item).pn_payment_item_id;
UPDATE pn_payment_schedules_all
SET transferred_by_user_id = l_last_updated_by,
transfer_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date
WHERE payment_schedule_id = exp_ap_cache(item).pn_payment_schedule_id;
lia_acnt_tab.delete;
acc_acnt_tab.delete;
exp_acnt_tab.delete;
/* alls well and we are ready to insert into the AP ITF
Get tax name for the expense account */
OPEN get_tax_name(nvl(exp_ap_cache(item).pn_tax_code_id,
exp_ap_cache(item).pn_tax_group_id));
l_context := 'Inserting into ap_invoices_interface ...';
pnp_debug_pkg.log(' Inserting into ap_invoices_interface ...');
INSERT INTO ap_invoices_interface
(invoice_id
,invoice_num
,invoice_amount
,invoice_currency_code
,description
,source
,vendor_id
,vendor_site_id
,accts_pay_code_combination_id
,last_updated_by
,last_update_date
,last_update_login
,created_by
,creation_date
,ORG_ID
,GROUP_ID
,gl_date
,terms_date
,invoice_date
,invoice_received_date
,terms_id
,legal_entity_id
,exchange_rate
,exchange_rate_type
,exchange_date
,CALC_TAX_DURING_IMPORT_FLAG
,ADD_TAX_TO_INV_AMT_FLAG)--For Bug 9068811
VALUES
(AP_INVOICES_INTERFACE_S.nextval
,'PN-'||PN_PAYMENT_ITEMS_NUM_S.nextval
,ROUND(exp_ap_cache(item).pn_export_currency_amount,l_precision)
,exp_ap_cache(item).pn_export_currency_code
,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
,'Oracle Property Manager'
,exp_ap_cache(item).pn_vendor_id
,exp_ap_cache(item).pn_vendor_site_id
,lia_acnt_tab(1).account_id
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_created_by
,l_creation_date
,exp_ap_cache(item).org_id
,p_group_id
,exp_ap_cache(item).gl_date -- gl_date
,exp_ap_cache(item).pn_due_date -- terms date
,exp_ap_cache(item).pn_due_date -- invoice date
,exp_ap_cache(item).pn_due_date -- invoice received date
,exp_ap_cache(item).pn_ap_ar_term_id
,exp_ap_cache(item).pn_legal_entity_id
,exp_ap_cache(item).conv_rate
,exp_ap_cache(item).conv_rate_type
,exp_ap_cache(item).pn_accounted_date
,'Y'
,'Y')--For Bug 9068811
RETURNING invoice_id, invoice_num, invoice_amount
INTO l_invoice_id, l_invoice_num, l_header_amount;
l_context := 'Inserting into ap_invoice_lines_interface ...';
INSERT INTO ap_invoice_lines_interface
(invoice_id
,invoice_line_id
,line_type_lookup_code
,amount
,description
,dist_code_combination_id
,DEFAULT_DIST_CCID
,last_updated_by
,last_update_date
,last_update_login
,created_by
,creation_date
,line_number
,org_id
,amount_includes_tax_flag -- Tax Inclusive
,distribution_set_id
,project_id
,task_id
,expenditure_type
,expenditure_item_date
,expenditure_organization_id
,tax_code_id
,tax_code
,tax_classification_code
,tax_code_override_flag)
values
(l_invoice_id
,AP_INVOICE_LINES_INTERFACE_S.nextval
,'ITEM'
,ROUND(((exp_ap_cache(item).pn_export_currency_amount)
* nvl(acc_acnt_tab(i).percentage,100)/100), l_precision)
,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
,acc_acnt_tab(i).account_id
,acc_acnt_tab(i).account_id
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_created_by
,l_creation_date
,l_lineNumber
,exp_ap_cache(item).org_id
,exp_ap_cache(item).pn_tax_included
,exp_ap_cache(item).pn_distribution_set_id
,exp_ap_cache(item).pn_project_id
,exp_ap_cache(item).pn_task_id
,exp_ap_cache(item).pn_expenditure_type
,exp_ap_cache(item).pn_expenditure_item_date
,exp_ap_cache(item).pn_organization_id
,nvl(exp_ap_cache(item).pn_tax_code_id,
exp_ap_cache(item).pn_tax_group_id)
,l_tax_name
,exp_ap_cache(item).pn_tax_classification_code
,l_tax_code_override_flag)
RETURNING invoice_line_id, amount
INTO l_invoice_line_id, l_line_amount;
l_context := 'Inserting into ap_invoice_lines_interface ...';
INSERT INTO ap_invoice_lines_interface
(invoice_id
,invoice_line_id
,line_type_lookup_code
,amount
,description
,dist_code_combination_id
,DEFAULT_DIST_CCID
,last_updated_by
,last_update_date
,last_update_login
,created_by
,creation_date
,line_number
,org_id
,amount_includes_tax_flag -- Tax Inclusive
,distribution_set_id
,project_id
,task_id
,expenditure_type
,expenditure_item_date
,expenditure_organization_id
,tax_code_id
,tax_code
,tax_classification_code
,tax_code_override_flag)
VALUES
(l_invoice_id
,AP_INVOICE_LINES_INTERFACE_S.nextval
,'ITEM'
,l_amt
,'Lease Number: ' || exp_ap_cache(item).pn_lease_num
,exp_acnt_tab(i).account_id
,exp_acnt_tab(i).account_id
,l_last_updated_by
,l_last_update_date
,l_last_update_login
,l_created_by
,l_creation_date
,l_lineNumber
,exp_ap_cache(item).org_id
,exp_ap_cache(item).pn_tax_included
,exp_ap_cache(item).pn_distribution_set_id
,exp_ap_cache(item).pn_project_id
,exp_ap_cache(item).pn_task_id
,exp_ap_cache(item).pn_expenditure_type
,exp_ap_cache(item).pn_expenditure_item_date
,exp_ap_cache(item).pn_organization_id
,nvl(exp_ap_cache(item).pn_tax_code_id,
exp_ap_cache(item).pn_tax_group_id)
,l_tax_name
,exp_ap_cache(item).pn_tax_classification_code
,l_tax_code_override_flag)
RETURNING invoice_line_id, amount
INTO l_invoice_line_id, l_line_amount;
UPDATE pn_payment_items_all
SET transferred_to_ap_flag = 'Y' ,
ap_invoice_num = l_invoice_num,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date ,
export_group_id = p_group_id
WHERE payment_item_id = exp_ap_cache(item).pn_payment_item_id;
UPDATE pn_payment_schedules_all
SET transferred_by_user_id = l_last_updated_by,
transfer_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date
WHERE payment_schedule_id = exp_ap_cache(item).pn_payment_schedule_id;
SELECT pi.grouping_rule_id
FROM pn_payment_items pi,
pn_payment_terms_all pt,
pn_leases_all le
WHERE pi.payment_term_id = pt.payment_term_id
AND pt.lease_id = le.lease_id
AND nvl(pi.export_to_ap_flag,'N') = 'Y'
AND pi.payment_item_type_lookup_code ='CASH'
AND le.parent_lease_id is NULL
AND pi.transferred_to_ap_flag is NULL
AND pi.vendor_id is NOT NULL
AND pi.export_currency_amount <> 0
AND pi.grouping_rule_id IS NOT NULL
GROUP BY pi.grouping_rule_id;
SELECT pt.grouping_rule_id
FROM pn_payment_items pi,
pn_payment_terms_all pt,
pn_leases_all le
WHERE pi.payment_term_id = pt.payment_term_id
AND pt.lease_id = le.lease_id
AND nvl(pi.export_to_ap_flag,'N') = 'Y'
AND pi.payment_item_type_lookup_code = 'CASH'
AND le.parent_lease_id is NULL
AND pi.transferred_to_ap_flag is NULL
AND pi.vendor_id is NOT NULL
AND pi.export_currency_amount <> 0
AND pi.grouping_rule_id IS NULL
AND pt.grouping_rule_id IS NOT NULL
GROUP BY pt.grouping_rule_id;
SELECT pld.grouping_rule_id
FROM pn_payment_items pi,
pn_payment_terms_all pt,
pn_leases_all le,
pn_lease_details_all pld
WHERE pi.payment_term_id = pt.payment_term_id
AND pt.lease_id = le.lease_id
AND pld.lease_id = le.lease_id
AND nvl(pi.export_to_ap_flag,'N') = 'Y'
AND pi.payment_item_type_lookup_code ='CASH'
AND le.parent_lease_id is NULL
AND pi.transferred_to_ap_flag is NULL
AND pi.vendor_id is NOT NULL
AND pi.export_currency_amount <> 0
AND pi.grouping_rule_id IS NULL
AND pt.grouping_rule_id IS NULL
AND pld.grouping_rule_id IS NOT NULL
GROUP BY pld.grouping_rule_id;
SELECT TO_CHAR(pn_payments_group_s.NEXTVAL)
INTO l_group_id
FROM dual;
Q_Payitem := l_Select_Clause ||
l_param_where_clause ||
l_where_clause_item ||
l_order_by_clause_item ||
l_order_by_clause ||
l_order_by_clause_grpby ||
' , lia_account';
Q_Payitem := l_Select_Clause ||
l_param_where_clause ||
l_where_clause_term ||
l_order_by_clause_term ||
l_order_by_clause ||
l_order_by_clause_grpby||
' , lia_account';
Q_Payitem := l_Select_Clause ||
l_param_where_clause ||
l_where_clause_lease ||
l_order_by_clause_lease ||
l_order_by_clause||
l_order_by_clause_grpby||
' , lia_account';
Q_Payitem := l_Select_Clause ||
l_param_where_clause ||
l_where_clause_sysop ||
l_order_by_clause_default;
Q_Payitem := l_Select_Clause ||
l_param_where_clause ||
l_where_clause_sysop ||
' ORDER BY ' ||
l_order_by_clause ||
l_order_by_clause_grpby||
' , lia_account';