[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT account_id,
account_class,
percentage
FROM pn_distributions_all
WHERE payment_term_id = p_term_id;
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'PN_PAYMENT_PURPOSE_TYPE'
AND lookup_code = p_lookup_code;
SELECT name,
type,
frequency
FROM ra_rules
WHERE rule_id = p_rule_id;
SELECT name
FROM ar_receipt_methods
WHERE receipt_method_id = p_receipt_method_id;
SELECT SALESREP_NUMBER,SALES_CREDIT_TYPE_ID
FROM ra_salesreps
WHERE salesrep_id = p_salesrep_id
AND org_id = p_org_id;
SELECT name
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_cust_trx_type_id;
SELECT name
FROM ra_terms
WHERE term_id = p_term_id;
SELECT pl.location_code
FROM pn_locations_all pl
WHERE pl.location_id = p_location_id;
SELECT name,
rev_acc_allocation_rule,
allow_sales_credit_flag,
derive_date_flag
FROM ra_batch_sources
WHERE batch_source_id = 24;
SELECT tax_code
FROM ar_vat_tax
WHERE vat_tax_id = p_tax_code_id;
SELECT account_class
FROM pn_distributions_all
WHERE payment_term_id = p_term_id;
SELECT 'Y'
FROM ra_account_defaults def,
ra_account_default_segments seg
WHERE seg.table_name = 'RA_SALESREPS'
AND def.gl_default_id = seg.gl_default_id
AND def.type = 'REV';
SELECT 'Y'
FROM ar_system_parameters
WHERE salesrep_required_flag = 'Y';
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
SELECT nvl(send_entries,'Y')
FROM pn_lease_details_all
WHERE lease_id = p_lease_id;
SELECT gb.COLUMN_ID
FROM RA_GROUP_BYS gb,
RA_GROUPING_TRX_TYPES gt,
RA_GROUPING_RULES gr,
RA_BATCH_SOURCES bs
WHERE gb.GROUPING_TRX_TYPE_ID = gt.GROUPING_TRX_TYPE_ID
AND gt.GROUPING_RULE_ID = gr.GROUPING_RULE_ID
AND gr.GROUPING_RULE_ID = bs.GROUPING_RULE_ID
AND bs.BATCH_SOURCE_ID = 24
AND NVL(bs.org_id,-99) = NVL(pn_mo_cache_utils.get_current_org_id,-99); --Bug#6319026
SELECT post_to_gl
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = p_trx_type_id
AND org_id = p_org_id;
g_cursor_select_grp INTEGER;
g_cursor_select_nogrp INTEGER;
Q_select_grp VARCHAR2(32000);
Q_select_nogrp VARCHAR2(32000);
'SELECT pi.payment_item_id, pi.payment_term_id,
pi.export_currency_code, pi.export_currency_amount,
pi.customer_id AS customer_id, hzc.cust_acct_site_id,
hzc1.cust_acct_site_id, pt.tax_code_id,
pt.tax_classification_code, pt.legal_entity_id AS legal_entity_id,
pt.inv_rule_id, pt.account_rule_id,
pt.ap_ar_term_id, pt.cust_trx_type_id AS cust_trx_type_id,
pt.receipt_method_id, pt.cust_po_number,
pt.tax_included, pt.salesrep_id,
pt.project_attribute_category,pt.project_attribute3,
pt.project_attribute4, pt.project_attribute5,
pt.project_attribute6, pt.project_attribute7,
pi.org_id AS org_id, le.lease_num,
pi.payment_schedule_id, ps.period_name,
pt.payment_purpose_code, le.lease_id,
pi.due_date, pt.normalize,
TRUNC(pi.accounted_date), pi.rate,
pt.location_id, NVL(pld.send_entries, ''Y''),
pd.account_id as rec_account,
TO_DATE(DECODE(pt.inv_rule_id||pt.account_rule_id||cust_trx.post_to_gl
,''Y'', to_char(PNP_UTIL_FUNC.Get_Start_Date(ps.period_name,
pn_mo_cache_utils.get_current_org_id)
, ''MM/DD/YYYY'')
,NULL)
,''MM/DD/YYYY'') as gl_date,
DECODE(UPPER('''||l_func_curr_code||''')
,UPPER(pi.export_currency_code),1
,DECODE(UPPER(PNP_UTIL_FUNC.check_conversion_type('''||l_func_curr_code||''',
pn_mo_cache_utils.get_current_org_id))
,''USER'',pi.rate
,NULL)) as conv_rate,
DECODE(UPPER('''||l_func_curr_code||''')
,UPPER(pi.export_currency_code),''User''
,PNP_UTIL_FUNC.check_conversion_type('''||l_func_curr_code||''',
pn_mo_cache_utils.get_current_org_id)
) as conv_rate_type,
pt.payment_purpose_code as payment_purpose,
pt.payment_term_type_code as payment_type,
TO_DATE(DECODE(rr.type||rr.frequency ,''ASPECIFIC'',NULL,
to_char(PNP_UTIL_FUNC.Get_Start_Date(ps.period_name,
pn_mo_cache_utils.get_current_org_id)
, ''MM/DD/YYYY''))
,''MM/DD/YYYY'') as rule_gl_date,
ps.schedule_date as schedule_date
FROM PN_PAYMENT_ITEMS pi, PN_PAYMENT_SCHEDULES_ALL ps,
PN_PAYMENT_TERMS_ALL pt, PN_LEASES_ALL le,
PN_LEASE_DETAILS_ALL pld, HZ_CUST_SITE_USES_ALL hzc,
HZ_CUST_SITE_USES_ALL hzc1, HZ_PARTIES party,
HZ_CUST_ACCOUNTS_ALL cust_acc, FND_LOOKUPS type_lookup,
FND_LOOKUPS purpose_lookup, HR_OPERATING_UNITS ou,
PN_DISTRIBUTIONS_ALL pd, RA_CUST_TRX_TYPES_ALL cust_trx,
RA_RULES rr
WHERE pi.payment_term_id = pt.payment_term_id
AND pi.payment_schedule_id = ps.payment_schedule_id
AND pi.export_to_ar_flag = ''Y''
and ps.payment_status_lookup_code =''APPROVED''
AND pi.payment_item_type_lookup_code = ''CASH''
AND pt.lease_id = le.lease_id
AND pld.lease_id = le.lease_id
AND le.lease_class_code <> ''DIRECT''
AND hzc.site_use_id = pi.customer_site_use_id
AND hzc1.site_use_id (+) = pi.cust_ship_site_id
AND NVL(pi.transferred_to_ar_flag, ''N'') = ''N''
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 party.party_id = cust_acc.party_id
AND cust_acc.cust_account_id = pi.customer_id
AND ou.organization_id = pi.org_id
AND pi.export_currency_amount <> 0
AND pd.payment_term_id = pt.payment_term_id
AND pd.account_class = ''REC''
AND pt.cust_trx_type_id = cust_trx.cust_trx_type_id
AND NVL(cust_trx.org_id,-99) = NVL(pt.org_id,NVL(cust_trx.org_id,-99))
AND rr.rule_id(+) = pt.account_rule_id
';
' SELECT pi.payment_item_id, pi.payment_term_id,
pi.export_currency_code, pi.export_currency_amount,
pi.customer_id AS customer_id, hzc.cust_acct_site_id,
hzc1.cust_acct_site_id, pt.tax_code_id,
pt.tax_classification_code, pt.legal_entity_id AS legal_entity_id,
pt.inv_rule_id, pt.account_rule_id,
pt.ap_ar_term_id, pt.cust_trx_type_id AS cust_trx_type_id,
pt.receipt_method_id, pt.cust_po_number,
pt.tax_included, pt.salesrep_id,
pt.project_attribute_category, pt.project_attribute3,
pt.project_attribute4, pt.project_attribute5,
pt.project_attribute6, pt.project_attribute7,
pi.org_id AS org_id,le.lease_num, pi.payment_schedule_id,
ps.period_name, pt.payment_purpose_code,
le.lease_id, pi.due_date,
pt.normalize, TRUNC(pi.accounted_date),pi.rate,
PT.Location_id,
pt.payment_purpose_code as payment_purpose,
pt.payment_term_type_code as payment_type,
ps.schedule_date as schedule_date
FROM PN_PAYMENT_ITEMS pi, PN_PAYMENT_SCHEDULES_ALL ps,
PN_PAYMENT_TERMS_ALL pt, PN_LEASES_ALL le,
HZ_CUST_SITE_USES_ALL hzc, HZ_CUST_SITE_USES_ALL hzc1,
hz_parties party, hz_cust_accounts_ALL cust_acc,
fnd_lookups type_lookup, fnd_lookups purpose_lookup,
hr_operating_units ou
WHERE pi.payment_term_id = pt.payment_term_id
AND pi.payment_schedule_id = ps.payment_schedule_id
AND pi.export_to_ar_flag = ''Y''
and ps.payment_status_lookup_code =''APPROVED''
AND pi.payment_item_type_lookup_code = ''CASH''
AND pt.lease_id = le.lease_id
AND le.lease_class_code <> ''DIRECT''
AND hzc.site_use_id = pi.customer_site_use_id
AND hzc1.site_use_id (+) = pi.cust_ship_site_id
AND NVL(pi.transferred_to_ar_flag, ''N'') = ''N''
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 party.party_id = cust_acc.party_id
AND cust_acc.cust_account_id = pi.customer_id
AND ou.organization_id = pi.org_id
AND pi.export_currency_amount <> 0 ' ;
SELECT TO_CHAR(pn_payments_group_s.NEXTVAL)
INTO l_groupId
FROM DUAL;
before inserting into interface_line_attribute2
-- 22-NOV-04 kkhegde o Bug 3751438 - fixed the validation for distributions
-- 22-DEC-04 Kiran o Fix for 3751438 - corrected it for bug # 4083036
-- 10-MAR-05 piagrawa o Bug #4231051 - Truncated the attribute values to 30
-- characters before inserting into ra_interface_lines,
-- ra_interface_salescredits and
-- ra_interface_distributions tables
-- 15-JUL-05 hareesha o Bug 4284035 - Replaced RA_INTERFACE_DISTRIBUTIONS_ALL
-- with _ALL table.
-- 11-OCT-05 pikhar o Bug 4652946 - Added trunc to pi.accounted_date in
-- Q_Billitem, l_ord_clause
-- 28-OCT-05 sdmahesh o ATG mandated changes for SQL literals
-- 24-MAR-06 Hareesha o Bug 5116270 Modified get_salesrep_number to pass
-- org_id as parameter.
-- 07-AUG-06 Hareesha o Bug #5405883 Inserted schedule_date as rule_start_date
-- into ra_interface_lines_all instead of rule_gl_date.
-- 29-Nov-12 ppenumar o Bug #14762515 Used the plsql table 'err_ar_tble' to
-- display the details of the failed billing items in the log.
-----------------------------------------------------------------------------*/
Procedure EXP_TO_AR_GRP (
errbuf IN OUT NOCOPY VARCHAR2
,retcode IN OUT NOCOPY VARCHAR2
,p_groupId VARCHAR2
,p_lease_num_low VARCHAR2
,p_lease_num_high VARCHAR2
,p_sch_dt_low VARCHAR2
,p_sch_dt_high VARCHAR2
,p_due_dt_low VARCHAR2
,p_due_dt_high VARCHAR2
,p_pay_prps_code VARCHAR2
,p_prd_name VARCHAR2
,p_amt_low NUMBER
,p_amt_high NUMBER
,p_customer_id NUMBER
,p_grp_param VARCHAR2
)
IS
l_acnt_cls PN_DISTRIBUTIONS.account_class%TYPE;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
l_last_update_date DATE := sysdate;
l_rec_insert_flag BOOLEAN := TRUE;
l_rows_select_grp NUMBER;
l_count_select_grp NUMBER;
le_ar_tbl.delete;
term_ar_tbl.delete;
Q_select_grp := 'SELECT payment_term_id,
customer_id,
cust_trx_type_id,
org_id
FROM ('||Q_Billitem_grp||')
WHERE legal_entity_id IS NULL';
g_cursor_select_grp := dbms_sql.open_cursor;
dbms_sql.parse(g_cursor_select_grp,Q_select_grp,dbms_sql.native);
do_binding (g_cursor_select_grp
,p_lease_num_low
,p_lease_num_high
,p_sch_dt_low
,p_sch_dt_high
,p_due_dt_low
,p_due_dt_high
,p_pay_prps_code
,p_prd_name
,p_amt_low
,p_amt_high
,p_customer_id
,p_grp_param
);
dbms_sql.define_column (g_cursor_select_grp,1,v_pn_payment_term_id1);
dbms_sql.define_column (g_cursor_select_grp,2,v_pn_customer_id1);
dbms_sql.define_column (g_cursor_select_grp,3,v_pn_trx_type_id1);
dbms_sql.define_column (g_cursor_select_grp,4,v_pn_org_id1);
l_rows_select_grp := dbms_sql.execute(g_cursor_select_grp);
l_count_select_grp := dbms_sql.fetch_rows(g_cursor_select_grp);
EXIT WHEN l_count_select_grp <> 1;
dbms_sql.column_value (g_cursor_select_grp, 1,term_ar_tbl(l_index));
dbms_sql.column_value (g_cursor_select_grp, 2,v_pn_customer_id1);
dbms_sql.column_value (g_cursor_select_grp,3,v_pn_trx_type_id1);
dbms_sql.column_value (g_cursor_select_grp,4,v_pn_org_id1);
UPDATE pn_payment_terms_all
SET legal_entity_id = le_ar_tbl(i)
WHERE payment_term_id = term_ar_tbl(i);
IF dbms_sql.is_open (g_cursor_select_grp) THEN
dbms_sql.close_cursor (g_cursor_select_grp);
exp_ar_tbl.DELETE;
err_ar_tbl.DELETE; --Bug 14762515
exp_ar_tbl.DELETE(l_index);
insert into AR interface tables appropriately
for all lines belonging to one group,
insert only one distribution for REC with 0 amount
end if
=== LOGIC === */
/* Initialize the counters */
l_start := 1;
l_rec_insert_flag := TRUE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_last_update_date := sysdate;
/* if we reached here, we have a group worth inserting
loop through the PL/SQL table and insert
o one record per item in group into ra_interface_lines
o distributions into ra_interface_distributions for REV
and UNEARN based on pn_distributions for each line
o ONLY one record in ra_interface_distributions for REC
for ALL items
*/
FOR l_grp IN l_start .. l_next-1 LOOP
/* Default the precision to 2 */
l_precision := 2;
l_context := 'Inserting into interface lines';
INSERT INTO ra_interface_lines_all
(amount_includes_tax_flag -- tax inclusive flag
,tax_code -- tax code
,legal_entity_id -- legal entity
,org_id -- org id
,gl_date -- gl date
,uom_code -- uom
,invoicing_rule_id -- invoicing rule id
,invoicing_rule_name -- invoicing rule name
,accounting_rule_id -- accounting rule id
,accounting_rule_name -- accounting rule name
,receipt_method_id -- payment method id
,receipt_method_name -- payment method name
,quantity -- quantity invoiced
,unit_selling_price -- unit selling price
,primary_salesrep_id -- primary sales person id
,primary_salesrep_number -- primary sales rep number
,purchase_order -- purchase order
,batch_source_name -- Batch source name
,set_of_books_id -- set of books id
,line_type -- line type
,description -- description
,currency_code -- currency code
,amount -- amount
,cust_trx_type_id -- transaction type id
,cust_trx_type_name -- transaction type name
,term_id -- payment term id
,term_name -- payment term name
,conversion_type
,conversion_rate
,conversion_date
,interface_line_context
,interface_line_attribute1
,interface_line_attribute2
,interface_line_attribute3
,interface_line_attribute4
,interface_line_attribute5
,interface_line_attribute6
,interface_line_attribute7
,interface_line_attribute8
,interface_line_attribute9
,interface_line_attribute10
,orig_system_bill_customer_id -- bill to customer id
,orig_system_bill_address_id -- bill to customer site address
,orig_system_ship_customer_id -- ship to customer id
,orig_system_ship_address_id -- ship to customer site address
,trx_date -- transaction date
,rule_start_date
)
VALUES
(exp_ar_tbl(l_grp).pn_tax_included
,l_tax_code
,exp_ar_tbl(l_grp).pn_legal_entity_id
,exp_ar_tbl(l_grp).pn_org_id
,exp_ar_tbl(l_grp).gl_date
,'EA'
,exp_ar_tbl(l_grp).pn_inv_rule_id
,l_inv_rule_name
,exp_ar_tbl(l_grp).pn_account_rule_id
,l_acc_rule_name
,exp_ar_tbl(l_grp).pn_pay_method_id
,l_pay_method_name
,1
,round(exp_ar_tbl(l_grp).pn_export_currency_amount,l_precision)
,exp_ar_tbl(l_grp).pn_salesrep_id
,l_salesrep_number
,exp_ar_tbl(l_grp).pn_po_number
,l_batch_name
,pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
pn_mo_cache_utils.get_current_org_id)
,'LINE'
,l_desc
,exp_ar_tbl(l_grp).pn_export_currency_code
,round(exp_ar_tbl(l_grp).pn_export_currency_amount,l_precision)
,exp_ar_tbl(l_grp).pn_trx_type_id
,l_cust_trx_name
,exp_ar_tbl(l_grp).pn_term_id
,l_term_name
,exp_ar_tbl(l_grp).conv_rate_type
,exp_ar_tbl(l_grp).conv_rate
,exp_ar_tbl(l_grp).pn_accounted_date
,'Property-Projects'
,SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num
, 1
, 150 - LENGTHB(' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)))
|| ' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)
,nvl(SUBSTRB(l_location_code,1,150),'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr3,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr4,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr5,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr6,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr7,'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_purpose, 1,150),'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_type, 1, 150),'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num, 1, 150),'N/A')
,exp_ar_tbl(l_grp).pn_customer_id
,exp_ar_tbl(l_grp).pn_customer_site_use_id
,exp_ar_tbl(l_grp).pn_customer_id
,exp_ar_tbl(l_grp).pn_cust_ship_site_id
,exp_ar_tbl(l_grp).transaction_date
,l_rule_start_date
);
/* Inserting data in RA_INTERFACE_SALESCREDITS */
IF exp_ar_tbl(l_grp).pn_salesrep_id IS NOT NULL
AND (l_gl_seg = 'Y'
OR l_sys_para = 'Y'
OR l_sal_cred = 'Y' ) THEN
INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
(
salesrep_id
,salesrep_number
,sales_credit_type_id
,sales_credit_percent_split
,org_id
,interface_line_context
,interface_line_attribute1
,interface_line_attribute2
,interface_line_attribute3
,interface_line_attribute4
,interface_line_attribute5
,interface_line_attribute6
,interface_line_attribute7
,interface_line_attribute8
,interface_line_attribute9
,interface_line_attribute10
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
exp_ar_tbl(l_grp).pn_salesrep_id
,l_salesrep_number
,l_sales_credit_id
,100
,exp_ar_tbl(l_grp).pn_org_id
,'Property-Projects'
,SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num
, 1
, 150 - LENGTHB(' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)))
|| ' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)
,nvl(SUBSTRB(l_location_code,1,150),'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr3,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr4,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr5,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr6,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr7,'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_purpose,1,150),'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_type,1,150),'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num,1,150),'N/A')
,l_last_updated_by
,sysdate
,l_last_updated_by
,sysdate
,l_last_update_login
);
/* Insert into Distributions for REC acount */
/* This has to be done only once for the grouped items */
IF l_rec_insert_flag THEN
l_context := 'Inserting into Distributions for REC acount for this group';
INSERT INTO ra_interface_distributions_all
(account_class
,percent
,amount
,code_combination_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,org_id
,interface_line_context
,interface_line_attribute1
,interface_line_attribute2
,interface_line_attribute3
,interface_line_attribute4
,interface_line_attribute5
,interface_line_attribute6
,interface_line_attribute7
,interface_line_attribute8
,interface_line_attribute9
,interface_line_attribute10
)
VALUES
('REC'
,100
,0
,exp_ar_tbl(l_grp).rec_account
,l_last_updated_by
,sysdate
,l_last_updated_by
,sysdate
,l_last_update_login
,exp_ar_tbl(l_grp).pn_org_id
,'Property-Projects'
,SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num
, 1
, 150 - LENGTHB(' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)))
|| ' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)
,nvl(SUBSTRB(l_location_code,1,150),'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr3,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr4,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr5,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr6,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr7,'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_purpose,1,150),'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_type,1,150),'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num,1,150),'N/A')
);
l_context := 'Inserted into Distributions for REC acount';
l_rec_insert_flag := FALSE;
END IF; -- if REC has not been inserted yet
pnp_debug_pkg.log('Inserting into distributions for account types of REV and UNEARN');
l_context := 'Inserting into Distributions for account types of REV and UNEARN';
INSERT INTO ra_interface_distributions_all
(account_class
,percent
,amount
,code_combination_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,org_id
,interface_line_context
,interface_line_attribute1
,interface_line_attribute2
,interface_line_attribute3
,interface_line_attribute4
,interface_line_attribute5
,interface_line_attribute6
,interface_line_attribute7
,interface_line_attribute8
,interface_line_attribute9
,interface_line_attribute10
)
VALUES
(l_acnt_cls
,l_percent
,round(l_amt,l_precision)
,acnt_rec.account_id
,l_last_updated_by
,sysdate
,l_last_updated_by
,sysdate
,l_last_update_login
,exp_ar_tbl(l_grp).pn_org_id
,'Property-Projects'
,SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num
, 1
, 150 - LENGTHB(' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)))
|| ' - ' ||to_char(exp_ar_tbl(l_grp).pn_payment_item_id)
,nvl(SUBSTRB(l_location_code,1,150),'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr3,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr4,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr5,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr6,'N/A')
,nvl(exp_ar_tbl(l_grp).pn_proj_attr7,'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_purpose,1,150),'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).payment_type,1,150),'N/A')
,nvl(SUBSTRB(exp_ar_tbl(l_grp).pn_lease_num,1,150),'N/A')
);
pnp_debug_pkg.log('Inserted into distributions for account types of REV and UNEARN');
UPDATE PN_PAYMENT_ITEMS_ALL
SET transferred_to_ar_flag = 'Y' ,
ar_ref_code = exp_ar_tbl(l_grp).pn_payment_item_id,
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_groupId
WHERE payment_item_id = exp_ar_tbl(l_grp).pn_payment_item_id;
pnp_debug_pkg.log('Could not update row for Payment_Item_Id = ' ||
exp_ar_tbl(l_grp).Pn_Payment_Item_Id) ;
UPDATE PN_PAYMENT_SCHEDULES_ALL
SET Transferred_By_User_Id = l_last_updated_by,
Transfer_Date = l_last_update_date
WHERE Payment_Schedule_Id = exp_ar_tbl(l_grp).PN_Payment_Schedule_Id;
pnp_debug_pkg.log('Could not update row for Payment_Schedule_Id = '
|| exp_ar_tbl(l_grp).PN_Payment_Schedule_Id) ;
END LOOP; -- Finished inserting a Group
exp_ar_tbl.DELETE;
select actual_amount into l_actual_amount
from pn_payment_items_all
where payment_item_id=err_ar_tbl(i).pn_payment_item_id;
before inserting into interface_line_attribute2
-- 22-NOV-04 kkhegde o Bug 3751438 - fixed the validation for distributions
-- 22-DEC-04 Kiran o Fix for 3751438 - corrected it for bug # 4083036
-- 10-MAR-05 piagrawa o Bug #4231051 - Truncated the attribute values to 30
-- characters before inserting into ra_interface_lines,
-- ra_interface_salescredits and
-- ra_interface_distributions tables
-- 12-SEP-05 Parag o Bug #4284035 Modified insert statement to include org_id
-- 11-OCT-05 pikhar o Bug 4652946 - Added trunc to pi.accounted_date in
-- Q_Billitem
-- 28-OCT-05 sdmahesh o ATG mandated changes for SQL literals
-- 28-NOV-05 sdmahesh o Passed org_id to GET_START_DATE,check_conversion_type
-- 24-MAR-06 Hareesha o Bug 5116270 Modified get_salesrep_number to pass
-- org_id as parameter.
-- 07-AUG-06 Hareesha o Bug #5405883 Inserted schedule_date as rule_start_date
-- into ra_interface_lines_all instead of rule_gl_date.
-- 29-Nov-12 ppenumar o Bug #14762515 Used the plsql table 'err_ar_tble' to
-- display the details of the failed billing items in the log.
-----------------------------------------------------------------------------*/
Procedure EXP_TO_AR_NO_GRP (
errbuf IN OUT NOCOPY VARCHAR2
,retcode IN OUT NOCOPY VARCHAR2
,p_groupId VARCHAR2
,p_lease_num_low VARCHAR2
,p_lease_num_high VARCHAR2
,p_sch_dt_low VARCHAR2
,p_sch_dt_high VARCHAR2
,p_due_dt_low VARCHAR2
,p_due_dt_high VARCHAR2
,p_pay_prps_code VARCHAR2
,p_prd_name VARCHAR2
,p_amt_low NUMBER
,p_amt_high NUMBER
,p_customer_id NUMBER
,p_grp_param VARCHAR2
)
IS
v_pn_lease_num PN_LEASES.lease_num%TYPE;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
l_last_update_date DATE := sysdate;
l_rows_select_nogrp NUMBER;
l_count_select_nogrp NUMBER;
Q_select_nogrp := 'SELECT payment_term_id,
customer_id,
cust_trx_type_id,
org_id
FROM ('||Q_Billitem_nogrp||')
WHERE legal_entity_id IS NULL';
g_cursor_select_nogrp := dbms_sql.open_cursor;
dbms_sql.parse(g_cursor_select_nogrp,Q_select_nogrp,dbms_sql.native);
do_binding (g_cursor_select_nogrp
,p_lease_num_low
,p_lease_num_high
,p_sch_dt_low
,p_sch_dt_high
,p_due_dt_low
,p_due_dt_high
,p_pay_prps_code
,p_prd_name
,p_amt_low
,p_amt_high
,p_customer_id
,p_grp_param
);
le_ar_tbl.delete;
term_ar_tbl.delete;
err_ar_tbl.DELETE; --Bug 14762515
dbms_sql.define_column (g_cursor_select_nogrp,1,v_pn_payment_term_id1);
dbms_sql.define_column (g_cursor_select_nogrp,2,v_pn_customer_id1);
dbms_sql.define_column (g_cursor_select_nogrp,3,v_pn_trx_type_id1);
dbms_sql.define_column (g_cursor_select_nogrp,4,v_pn_org_id1);
l_rows_select_nogrp := dbms_sql.execute(g_cursor_select_nogrp);
l_count_select_nogrp := dbms_sql.fetch_rows(g_cursor_select_nogrp);
EXIT WHEN l_count_select_nogrp <> 1;
dbms_sql.column_value (g_cursor_select_nogrp,1,term_ar_tbl(l_index));
dbms_sql.column_value (g_cursor_select_nogrp,2,v_pn_customer_id1);
dbms_sql.column_value (g_cursor_select_nogrp,3,v_pn_trx_type_id1);
dbms_sql.column_value (g_cursor_select_nogrp,4,v_pn_org_id1);
UPDATE pn_payment_terms_all
SET legal_entity_id = le_ar_tbl(i)
WHERE payment_term_id = term_ar_tbl(i);
IF dbms_sql.is_open (g_cursor_select_nogrp) THEN
dbms_sql.close_cursor (g_cursor_select_nogrp);
l_context := 'Inserting into interface lines';
INSERT INTO ra_interface_lines_all
( amount_includes_tax_flag -- tax inclusive flag
,tax_code -- tax code
,legal_entity_id -- legal entity id
,org_id -- org id
,gl_date -- gl date
,uom_code -- uom
,invoicing_rule_id -- invoicing rule id
,invoicing_rule_name -- invoicing rule name
,accounting_rule_id -- accounting rule id
,accounting_rule_name -- accounting rule name
,receipt_method_id -- payment method id
,receipt_method_name -- payment method name
,quantity -- quantity invoiced
,unit_selling_price -- unit selling price
,primary_salesrep_id -- primary sales person id
,primary_salesrep_number -- primary sales rep number
,purchase_order -- purchase order
,batch_source_name -- Batch source name
,set_of_books_id -- set of books id
,line_type -- line type
,description -- description
,currency_code -- currency code
,amount -- amount
,cust_trx_type_id -- transaction type id
,cust_trx_type_name -- transaction type name
,term_id -- payment term id
,term_name -- payment term name
,conversion_type
,conversion_rate
,conversion_date
,interface_line_context
,interface_line_attribute1
,interface_line_attribute2
,interface_line_attribute3
,interface_line_attribute4
,interface_line_attribute5
,interface_line_attribute6
,interface_line_attribute7
,interface_line_attribute8
,interface_line_attribute9
,interface_line_attribute10
,orig_system_bill_customer_id -- bill to customer id
,orig_system_bill_address_id -- bill to customer site address
,orig_system_ship_customer_id -- ship to customer id
,orig_system_ship_address_id -- ship to customer site address
,trx_date -- transaction date
,rule_start_date
)
VALUES
( v_pn_tax_included
,l_tax_code
,v_pn_le
,v_pn_org_id
,l_start_date
,'EA'
,v_pn_inv_rule_id
,l_inv_rule_name
,v_pn_account_rule_id
,l_acc_rule_name
,v_pn_pay_method_id
,l_pay_method_name
,1
,round(v_pn_export_currency_amount,l_precision)
,v_pn_salesrep_id
,l_salesrep_number
,v_pn_po_number
,l_batch_name
,pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
pn_mo_cache_utils.get_current_org_id)
,'LINE'
,l_desc
,v_pn_export_currency_code
,round(v_pn_export_currency_amount,l_precision)
,v_pn_trx_type_id
, l_cust_trx_name
,v_pn_term_id
,l_term_name
,l_conv_rate_type
,l_conv_rate
,v_pn_accounted_date
,'Property-Projects'
,SUBSTRB(v_pn_lease_num
, 1
, 150 - LENGTHB( ' - ' ||to_char(v_pn_payment_item_id)))
|| ' - ' ||to_char(v_pn_payment_item_id)
,nvl(SUBSTRB(l_location_code,1,150),'N/A')
,nvl(v_pn_proj_attr3,'N/A')
,nvl(v_pn_proj_attr4,'N/A')
,nvl(v_pn_proj_attr5,'N/A')
,nvl(v_pn_proj_attr6,'N/A')
,nvl(v_pn_proj_attr7,'N/A')
,nvl(SUBSTRB(v_pur_code,1,150),'N/A')
,nvl(SUBSTRB(v_pur_type_code,1,150),'N/A')
,nvl(SUBSTRB(v_pn_lease_num,1,150),'N/A')
,v_pn_customer_id
,v_pn_customer_site_use_id
,v_pn_customer_id
,v_pn_cust_ship_site_id
,v_transaction_date
,l_rule_start_date
);
/* Inserting data in RA_INTERFACE_SALESCREDITS */
IF v_pn_salesrep_id IS NOT NULL
AND (l_gl_seg = 'Y'
OR l_sys_para = 'Y'
OR l_sal_cred = 'Y' ) THEN
INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
(
salesrep_id
,salesrep_number
,sales_credit_type_id
,sales_credit_percent_split
,interface_line_context
,interface_line_attribute1
,interface_line_attribute2
,interface_line_attribute3
,interface_line_attribute4
,interface_line_attribute5
,interface_line_attribute6
,interface_line_attribute7
,interface_line_attribute8
,interface_line_attribute9
,interface_line_attribute10
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,org_id
)
VALUES
(
v_pn_salesrep_id
,l_salesrep_number
,l_sales_credit_id
,100
,'Property-Projects'
, SUBSTRB(v_pn_lease_num
, 1
, 150 - LENGTHB(' - ' ||to_char(v_pn_payment_item_id)))
|| ' - ' ||to_char(v_pn_payment_item_id)
,NVL(SUBSTRB(l_location_code,1,150),'N/A')
,nvl(v_pn_proj_attr3,'N/A')
,nvl(v_pn_proj_attr4,'N/A')
,nvl(v_pn_proj_attr5,'N/A')
,nvl(v_pn_proj_attr6,'N/A')
,nvl(v_pn_proj_attr7,'N/A')
,NVL(SUBSTRB(v_pur_code,1,150),'N/A')
,NVL(SUBSTRB(v_pur_type_code,1,150),'N/A')
,NVL(SUBSTRB(v_pn_lease_num,1,150),'N/A')
,l_last_updated_by
,sysdate
,l_last_updated_by
,sysdate
,l_last_update_login
,v_pn_org_id
);
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_last_update_date := sysdate;
pnp_debug_pkg.log('Inserting into distributions');
l_context := 'Inserting into Distributions';
INSERT INTO ra_interface_distributions_all
( account_class
,percent
,amount
,code_combination_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,org_id
,interface_line_context
,interface_line_attribute1
,interface_line_attribute2
,interface_line_attribute3
,interface_line_attribute4
,interface_line_attribute5
,interface_line_attribute6
,interface_line_attribute7
,interface_line_attribute8
,interface_line_attribute9
,interface_line_attribute10
)
VALUES
( l_acnt_cls
,l_percent
,ROUND(l_amt,l_precision)
,acnt_rec.account_id
,l_last_updated_by
,SYSDATE
,l_last_updated_by
,SYSDATE
,l_last_update_login
,v_pn_org_id
,'Property-Projects'
, SUBSTRB(v_pn_lease_num
, 1
, 150 - LENGTHB(' - ' ||to_char(v_pn_payment_item_id)))
|| ' - ' ||to_char(v_pn_payment_item_id)
,NVL(SUBSTRB(l_location_code,1,150),'N/A')
,nvl(v_pn_proj_attr3,'N/A')
,nvl(v_pn_proj_attr4,'N/A')
,nvl(v_pn_proj_attr5,'N/A')
,nvl(v_pn_proj_attr6,'N/A')
,nvl(v_pn_proj_attr7,'N/A')
,NVL(SUBSTRB(v_pur_code,1,150),'N/A')
,NVL(SUBSTRB(v_pur_type_code,1,150),'N/A')
,NVL(SUBSTRB(v_pn_lease_num,1,150),'N/A')
);
pnp_debug_pkg.log('Inserted into distributions');
UPDATE PN_PAYMENT_ITEMS_ALL
SET transferred_to_ar_flag = 'Y' ,
ar_ref_code = v_pn_payment_item_id,
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_groupId
WHERE payment_item_id = v_pn_payment_item_id;
pnp_debug_pkg.log('Could not update row for Payment_Item_Id = ' ||
V_PN_Payment_Item_Id) ;
UPDATE PN_PAYMENT_SCHEDULES_ALL
SET Transferred_By_User_Id = l_last_updated_by,
Transfer_Date = l_last_update_date
WHERE Payment_Schedule_Id = V_PN_Payment_Schedule_Id;
pnp_debug_pkg.log('Could not update row for Payment_Schedule_Id = '
|| V_PN_Payment_Schedule_Id) ;
select actual_amount into l_actual_amount
from pn_payment_items_all
where payment_item_id=err_ar_tbl(i).pn_payment_item_id;