[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pt.payment_term_id,
pt.lease_id,
pi.payment_item_id,
pi.accounted_date,
ps.schedule_date,
pt.legal_entity_id,
pt.set_of_books_id,
pi.org_id,
ps.payment_schedule_id,
pi1.actual_amount
FROM PN_PAYMENT_TERMS pt,
PN_LEASES_ALL le ,
PN_PAYMENT_ITEMS_ALL pi,
PN_PAYMENT_ITEMS_ALL pi1,
PN_PAYMENT_SCHEDULES_ALL ps
WHERE pt.lease_id = le.lease_id
AND le.lease_class_code in ('THIRD_PARTY','SUB_LEASE')
AND LE.LEASE_ID BETWEEN P_LOW_LEASE_ID AND P_HIGH_LEASE_ID
AND ps.lease_id = le.lease_id
AND pi.payment_schedule_id = ps.payment_schedule_id
AND pi.payment_term_id = pt.payment_term_id
AND ps.payment_Status_lookup_code ='APPROVED'
AND ps.schedule_date between p_sch_start_date and p_sch_end_date
AND ps.period_name = nvl(p_period_name ,ps.period_name)
AND pi.payment_item_type_lookup_code = 'NORMALIZED'
AND pi.transferred_to_ar_flag is NULL
AND PT.NORMALIZE = 'Y'
AND LE.STATUS ='F'
AND pt.customer_id = nvl(p_customer_id,pt.customer_id)
AND pi1.payment_schedule_id = pi.payment_schedule_id
AND pi1.payment_term_id = pi.payment_term_id
AND pi1.payment_item_type_lookup_code = 'CASH'
AND ((pi1.transferred_to_ar_flag ='Y' AND pi1.actual_Amount <>0 )
OR (pi.transferred_to_ar_flag IS NULL AND pi1.actual_Amount = 0 ))
ORDER BY ps.payment_schedule_id;
SELECT pt.payment_term_id payment_term_id,
pt.legal_entity_id legal_entity_id,
pt.org_id org_id,
pt.customer_id customer_id,
pt.cust_trx_type_id cust_trx_type_id
FROM PN_PAYMENT_TERMS pt,
PN_LEASES_ALL le ,
PN_PAYMENT_ITEMS_ALL pi,
PN_PAYMENT_ITEMS_ALL pi1,
PN_PAYMENT_SCHEDULES_ALL ps
WHERE pt.lease_id = le.lease_id
AND le.lease_class_code in ('THIRD_PARTY','SUB_LEASE')
AND LE.LEASE_ID BETWEEN P_LOW_LEASE_ID AND P_HIGH_LEASE_ID
AND ps.lease_id = le.lease_id
AND pi.payment_schedule_id = ps.payment_schedule_id
AND pi.payment_term_id = pt.payment_term_id
AND ps.payment_Status_lookup_code ='APPROVED'
AND ps.schedule_date between p_sch_start_date and p_sch_end_date
AND ps.period_name = nvl(p_period_name ,ps.period_name)
AND pi.payment_item_type_lookup_code = 'NORMALIZED'
AND pi.transferred_to_ar_flag is NULL
AND PT.NORMALIZE = 'Y'
AND LE.STATUS ='F'
AND pt.customer_id = nvl(p_customer_id,pt.customer_id)
AND pi1.payment_schedule_id = pi.payment_schedule_id
AND pi1.payment_term_id = pi.payment_term_id
AND pi1.payment_item_type_lookup_code = 'CASH'
AND ((pi1.transferred_to_ar_flag ='Y' AND pi1.actual_amount <>0 )
OR (pi.transferred_to_ar_flag IS NULL AND pi1.actual_amount = 0 ))
AND pt.legal_entity_id IS NULL
ORDER BY pt.payment_term_id ;
SELECT pt.payment_term_id,
pt.lease_id,
pt.set_of_books_id,
pt.legal_entity_id,
pi.payment_item_id,
pi.due_date,
ps.schedule_date,
pi.org_id,
ps.payment_schedule_id,
pi1.actual_amount
FROM pn_payment_terms pt,
pn_leases_all le ,
pn_payment_items_all pi,
pn_payment_items_all pi1,
pn_payment_schedules_all ps
WHERE pt.lease_id = le.lease_id
AND le.lease_class_code = 'DIRECT'
and LE.LEASE_ID BETWEEN P_LOW_LEASE_ID AND P_HIGH_LEASE_ID
and ps.lease_id = le.lease_id
and pi.payment_schedule_id = ps.payment_schedule_id
and pi.payment_term_id = pt.payment_term_id
and ps.payment_Status_lookup_code ='APPROVED'
and ps.schedule_date BETWEEN p_sch_start_date AND p_sch_end_date
and ps.period_name = nvl(p_period_name ,ps.period_name)
AND pi.payment_item_type_lookup_code = 'NORMALIZED'
and pi.transferred_to_ap_flag IS NULL
and PT.NORMALIZE = 'Y'
AND LE.STATUS ='F'
AND LE.parent_lease_id IS NULL
and pt.vendor_id = nvl(p_vendor_id,pt.vendor_id)
and pi1.payment_schedule_id = pi.payment_schedule_id
and pi1.payment_term_id = pi.payment_term_id
and pi1.payment_item_type_lookup_code = 'CASH'
and ((pi1.transferred_to_ap_flag ='Y' AND pi1.actual_Amount <>0 )
OR (pi.transferred_to_ap_flag IS NULL AND pi1.actual_Amount = 0 ))
ORDER BY ps.payment_schedule_id;
SELECT pt.payment_term_id payment_term_id,
pt.legal_entity_id legal_entity_id,
pt.org_id org_id,
pt.vendor_id vendor_id,
pt.vendor_site_id vendor_site_id
FROM pn_payment_terms pt,
pn_leases_all le ,
pn_payment_items_all pi,
pn_payment_items_all pi1,
pn_payment_schedules_all ps
WHERE pt.lease_id = le.lease_id
AND le.lease_class_code = 'DIRECT'
AND LE.LEASE_ID BETWEEN P_LOW_LEASE_ID AND P_HIGH_LEASE_ID
AND ps.lease_id = le.lease_id
AND pi.payment_schedule_id = ps.payment_schedule_id
AND pi.payment_term_id = pt.payment_term_id
AND ps.payment_Status_lookup_code ='APPROVED'
AND ps.schedule_date BETWEEN p_sch_start_date AND p_sch_end_date
AND ps.period_name = nvl(p_period_name ,ps.period_name)
AND pi.payment_item_type_lookup_code = 'NORMALIZED'
AND pi.transferred_to_ap_flag IS NULL
AND PT.NORMALIZE = 'Y'
AND LE.STATUS ='F'
AND LE.parent_lease_id IS NULL
AND pt.vendor_id = nvl(p_vendor_id,pt.vendor_id)
AND pi1.payment_schedule_id = pi.payment_schedule_id
AND pi1.payment_term_id = pi.payment_term_id
AND pi1.payment_item_type_lookup_code = 'CASH'
AND ((pi1.transferred_to_ap_flag ='Y' AND pi1.actual_Amount <>0 )
OR (pi.transferred_to_ap_flag IS NULL AND pi1.actual_Amount = 0 ))
AND pt.legal_entity_id IS NULL
ORDER BY pt.payment_term_id;
SELECT lease_num
FROM pn_leases_all
WHERE lease_id = p_lease_id;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_update_date DATE;
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);
item_id_tbl.DELETE;
schedule_id_tbl.DELETE;
event_id_tbl.DELETE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_last_update_date := SYSDATE;
UPDATE pn_payment_items_all
SET transferred_to_ap_flag = 'Y',
xla_event_id = event_id_tbl(i),
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date
WHERE payment_item_id = item_id_tbl(i);
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 = schedule_id_tbl(i);
item_id_tbl.DELETE;
schedule_id_tbl.DELETE;
event_id_tbl.DELETE;
UPDATE pn_payment_items_all
SET transferred_to_ap_flag = 'Y' ,
xla_event_id = event_id_tbl(i),
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date
WHERE payment_item_id = item_id_tbl(i);
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 = schedule_id_tbl(i);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_update_date DATE;
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);
item_id_tbl.DELETE;
schedule_id_tbl.DELETE;
event_id_tbl.DELETE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_last_update_date := SYSDATE;
UPDATE pn_payment_items_all
SET transferred_to_ar_flag = 'Y' ,
xla_event_id = event_id_tbl(i),
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date
WHERE payment_item_id = item_id_tbl(i);
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 = schedule_id_tbl(i);
item_id_tbl.DELETE;
schedule_id_tbl.DELETE;
event_id_tbl.DELETE;
UPDATE pn_payment_items_all
SET transferred_to_ar_flag = 'Y' ,
xla_event_id = event_id_tbl(i),
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date
WHERE payment_item_id = item_id_tbl(i);
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 = schedule_id_tbl(i);
P_selection_type in varchar2,
p_gl_transfer_mode in varchar2 ,
p_submit_journal_import in varchar2 ,
p_process_days in varchar2,
p_debug_flag in varchar2 ,
P_validate_account in varchar2 ,
P_Org_id IN NUMBER
) as
p_default_period varchar2(250);
SELECT period_name
FROM gl_period_statuses
WHERE closing_status IN ('O', 'F')
AND set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
pn_mo_cache_utils.get_current_org_id)
AND application_id = 101
AND adjustment_period_flag = 'N'
AND fnd_date.canonical_to_date(p_default_gl_date) BETWEEN start_date AND end_date;
SELECT min(accounting_date),
max(accounting_date)
FROM pn_ae_headers_all pnh,
pn_ae_lines_all pnl
WHERE pnh.ae_header_id = pnl.ae_header_id
AND pnl.gl_sl_link_id IS NULL ;
fnd_message.set_token ('TYPE', p_selection_type);
P_selection_type ,
P_batch_name ,
trunc(l_from_date) ,
trunc(l_to_date) ,
P_validate_account ,
p_gl_transfer_mode ,
p_submit_journal_import ,
p_process_days ,
p_debug_flag
);
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 currency_code ,chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
SELECT account_id,
account_class,
percentage
FROM pn_distributions_all
WHERE payment_term_id = p_term_id;
SELECT nvl(send_entries, 'Y')
FROM pn_lease_details_all
WHERE lease_id = p_lease_id;
SELECT pt.payment_term_id,
pt.ap_ar_term_id,
pt.cust_trx_type_id,
le.lease_id,
pt.normalize,
PT.EVENT_TYPE_CODE,
pi.payment_item_id,
pi.currency_code,
pi.export_currency_amount,
pi.export_currency_code,
pi.payment_schedule_id,
ps.period_name,
pi.due_date,
pi.accounted_date,
pi.rate,
pi.accounted_amount,
pi1.actual_amount,
ps.schedule_date
FROM PN_PAYMENT_TERMS pt,
PN_LEASES_ALL le ,
PN_PAYMENT_ITEMS_ALL pi,
PN_PAYMENT_ITEMS_ALL pi1,
PN_PAYMENT_SCHEDULES_ALL ps
WHERE pt.lease_id = le.lease_id
AND le.lease_class_code in ('THIRD_PARTY','SUB_LEASE')
AND LE.LEASE_ID BETWEEN L_LOW_LEASE_ID AND L_HIGH_LEASE_ID
AND ps.lease_id = le.lease_id
AND pi.payment_schedule_id = ps.payment_schedule_id
AND pi.payment_term_id = pt.payment_term_id
AND ps.payment_Status_lookup_code ='APPROVED'
AND ps.schedule_date between l_sch_start_date and l_sch_end_date
AND ps.period_name = nvl(p_period_name ,ps.period_name)
AND pi.payment_item_type_lookup_code = 'NORMALIZED'
AND pi.transferred_to_ar_flag is NULL
AND PT.NORMALIZE = 'Y'
AND LE.STATUS ='F'
AND pt.customer_id = nvl(p_customer_id,pt.customer_id)
AND pi1.payment_schedule_id = pi.payment_schedule_id
AND pi1.payment_term_id = pi.payment_term_id
AND pi1.payment_item_type_lookup_code = 'CASH'
AND ((pi1.transferred_to_ar_flag ='Y' AND pi1.actual_Amount <>0 )
OR (pi.transferred_to_ar_flag IS NULL AND pi1.actual_Amount = 0 ))
ORDER BY pt.payment_term_id ;
SELECT 1
FROM gl_period_statuses
WHERE closing_status IN ('O', 'F')
AND set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
pn_mo_cache_utils.get_current_org_id)
AND application_id = 101
AND adjustment_period_flag = 'N'
AND period_name = v_pn_period_name;
acc_acnt_tab.DELETE;
rev_acnt_tab.DELETE;
SELECT nvl(max(event_number),0) + 1
INTO l_event_number
FROM PN_ACCOUNTING_EVENTS_ALL
WHERE source_table = 'PN_PAYMENT_TERMS'
AND SOURCE_ID = v_pn_payment_term_id
AND EVENT_TYPE_CODE = v_pn_event_type_code;
pnp_debug_pkg.log('Before event insert');
INSERT INTO PN_ACCOUNTING_EVENTS_ALL
(
ACCOUNTING_EVENT_ID ,
EVENT_TYPE_CODE ,
ACCOUNTING_DATE ,
EVENT_NUMBER ,
EVENT_STATUS_CODE ,
SOURCE_TABLE ,
SOURCE_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
PROGRAM_UPDATE_DATE ,
PROGRAM_ID ,
PROGRAM_APPLICATION_ID ,
REQUEST_ID ,
ORG_ID ,
CANNOT_ACCOUNT_FLAG
)
VALUES
(PN_ACCOUNTING_EVENTS_S.nextval,
nvl(V_PN_EVENT_TYPE_CODE ,'ABS'),
SYSDATE,
l_event_number,
'ACCOUNTED',
'PN_PAYMENT_TERMS',
l_term_id,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
SYSDATE,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_request_id,
pn_mo_cache_utils.get_current_org_id,
NULL
)
RETURNING ACCOUNTING_EVENT_ID INTO l_EVENT_id ;
pnp_debug_pkg.log('Before header insert');
INSERT INTO PN_AE_HEADERS_ALL
(AE_HEADER_ID ,
ACCOUNTING_EVENT_ID ,
SET_OF_BOOKS_ID ,
AE_CATEGORY ,
CROSS_CURRENCY_FLAG ,
PERIOD_NAME ,
ACCOUNTING_DATE ,
GL_TRANSFER_FLAG ,
GL_TRANSFER_RUN_ID ,
DESCRIPTION ,
ORG_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
PROGRAM_UPDATE_DATE ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
REQUEST_ID ,
ACCOUNTING_ERROR_CODE
)
VALUES
(PN_AE_HEADERS_S.nextval,
l_EVENT_ID,
l_set_of_books_id ,
'PM REVENUE' ,
'N',
l_period_name,
l_start_date,
'N',
-1,
l_header_desc,
pn_mo_cache_utils.get_current_org_id,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
SYSDATE,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.conc_request_id,
NULL
)
RETURNING AE_HEADER_ID INTO l_header_id;
pnp_debug_pkg.log('Inserting into lines for Revenue');
INSERT INTO PN_AE_LINES_ALL
(
AE_LINE_ID ,
AE_HEADER_ID ,
AE_LINE_NUMBER ,
AE_LINE_TYPE_CODE ,
CODE_COMBINATION_ID ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
ENTERED_DR ,
ENTERED_CR ,
ACCOUNTED_DR ,
ACCOUNTED_CR ,
SOURCE_TABLE ,
SOURCE_ID ,
DESCRIPTION ,
ACCOUNTING_ERROR_CODE ,
ORG_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
PROGRAM_UPDATE_DATE ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
REQUEST_ID
)
VALUES
(
PN_AE_LINES_S.NEXTVAL,
L_HEADER_ID,
l_rev_number,
rev_acnt_tab(i).account_class,
rev_acnt_tab(i).account_id,
v_pn_export_currency_code,
l_conv_rate_type ,
v_pn_accounted_date,
v_pn_rate,
null,
l_amt,
null,
l_accounted_amt,
'PN_PAYMENT_ITEMS',
V_PN_PAYMENT_ITEM_ID,
l_line_desc,
NULL,
pn_mo_cache_utils.get_current_org_id,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
SYSDATE,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.conc_request_id
);
pnp_debug_pkg.log('Inserted into lines for Revenue');
pnp_debug_pkg.log('Inserting into lines for Accrued Asset');
INSERT INTO PN_AE_LINES_ALL
(
AE_LINE_ID ,
AE_HEADER_ID ,
AE_LINE_NUMBER ,
AE_LINE_TYPE_CODE ,
CODE_COMBINATION_ID ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
ENTERED_DR ,
ENTERED_CR ,
ACCOUNTED_DR ,
ACCOUNTED_CR ,
SOURCE_TABLE ,
SOURCE_ID ,
DESCRIPTION ,
ACCOUNTING_ERROR_CODE ,
ORG_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
PROGRAM_UPDATE_DATE ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
REQUEST_ID
)
VALUES (
PN_AE_LINES_S.nextval,
L_HEADER_ID,
l_unearn_number,
acc_acnt_tab(i).account_class ,
acc_acnt_tab(i).account_id,
v_pn_export_currency_code,
l_conv_rate_type ,
v_pn_accounted_date,
v_pn_rate,
l_amt,
null,
l_accounted_amt,
null,
'PN_PAYMENT_ITEMS',
V_PN_PAYMENT_ITEM_ID,
l_line_desc,
null,
pn_mo_cache_utils.get_current_org_id,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
SYSDATE,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.conc_request_id
);
pnp_debug_pkg.log('Inserted into lines for Accrued Asset');
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
WHERE 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,
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 = V_PN_Payment_Schedule_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 currency_code ,chart_of_accounts_id
FROM gl_sets_of_books
where set_of_books_id = p_set_of_books_id;
SELECT account_id,
account_class,
percentage
FROM pn_distributions_all
WHERE payment_term_id = p_term_id;
SELECT nvl(send_entries, 'Y')
FROM pn_lease_details_all
WHERE lease_id = p_lease_id;
SELECT pt.payment_term_id,
pt.project_id,
pt.distribution_set_id,
le.lease_id,
pt.normalize,
PT.EVENT_TYPE_CODE,
pi.payment_item_id,
pi.currency_code,
pi.export_currency_amount,
pi.export_currency_code,
pi.payment_schedule_id,
ps.period_name,
pi.due_date,
pi.accounted_date,
pi.rate,
pi.accounted_amount,
pi1.actual_amount,
ps.schedule_date
FROM pn_payment_terms pt,
pn_leases_all le ,
pn_payment_items_all pi,
pn_payment_items_all pi1,
pn_payment_schedules_all ps
WHERE pt.lease_id = le.lease_id
AND le.lease_class_code = 'DIRECT'
and LE.LEASE_ID BETWEEN L_LOW_LEASE_ID AND L_HIGH_LEASE_ID
and ps.lease_id = le.lease_id
and pi.payment_schedule_id = ps.payment_schedule_id
and pi.payment_term_id = pt.payment_term_id
and ps.payment_Status_lookup_code ='APPROVED'
and ps.schedule_date between l_sch_start_date and l_sch_end_date
and ps.period_name = nvl(p_period_name ,ps.period_name)
AND pi.payment_item_type_lookup_code = 'NORMALIZED'
and pi.transferred_to_ap_flag is NULL
and PT.NORMALIZE = 'Y'
AND LE.STATUS ='F'
AND LE.parent_lease_id is NULL
and pt.vendor_id = nvl(p_vendor_id,pt.vendor_id)
and pi1.payment_schedule_id = pi.payment_schedule_id
and pi1.payment_term_id = pi.payment_term_id
and pi1.payment_item_type_lookup_code = 'CASH'
and ((pi1.transferred_to_ap_flag ='Y' and pi1.actual_Amount <>0 )
or (pi.transferred_to_ap_flag is NULL and pi1.actual_Amount = 0 ))
order by pt.payment_term_id;
SELECT 1
FROM gl_period_statuses
WHERE closing_status IN ('O', 'F')
AND set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
pn_mo_cache_utils.get_current_org_id)
AND application_id = 101
AND adjustment_period_flag = 'N'
AND period_name = v_pn_period_name;
acc_acnt_tab.DELETE;
exp_acnt_tab.DELETE;
SELECT nvl(max(event_number),0) + 1
INTO l_event_number
FROM pn_accounting_events_all
WHERE source_table = 'PN_PAYMENT_TERMS'
AND SOURCE_ID = v_pn_payment_term_id
AND EVENT_TYPE_CODE = v_pn_event_type_code;
pnp_debug_pkg.log('Before event insert');
l_context := 'Inserting into PN_ACCOUNTING_EVENTS';
INSERT INTO PN_ACCOUNTING_EVENTS_ALL
(
ACCOUNTING_EVENT_ID ,
EVENT_TYPE_CODE ,
ACCOUNTING_DATE ,
EVENT_NUMBER ,
EVENT_STATUS_CODE ,
SOURCE_TABLE ,
SOURCE_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
PROGRAM_UPDATE_DATE ,
PROGRAM_ID ,
PROGRAM_APPLICATION_ID ,
REQUEST_ID ,
ORG_ID ,
CANNOT_ACCOUNT_FLAG
)
VALUES
(
PN_ACCOUNTING_EVENTS_S.nextval,
nvl(V_PN_EVENT_TYPE_CODE ,'ABS') ,
SYSDATE,
l_event_number,
'ACCOUNTED',
'PN_PAYMENT_TERMS',
v_pn_payment_term_id,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
SYSDATE,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_request_id,
pn_mo_cache_utils.get_current_org_id,
NULL
)
RETURNING ACCOUNTING_EVENT_ID INTO l_EVENT_id ;
pnp_debug_pkg.log('Before header insert');
INSERT INTO PN_AE_HEADERS_ALL
(
AE_HEADER_ID ,
ACCOUNTING_EVENT_ID ,
SET_OF_BOOKS_ID ,
AE_CATEGORY ,
CROSS_CURRENCY_FLAG ,
PERIOD_NAME ,
ACCOUNTING_DATE ,
GL_TRANSFER_FLAG ,
GL_TRANSFER_RUN_ID ,
DESCRIPTION ,
ORG_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
PROGRAM_UPDATE_DATE ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
REQUEST_ID ,
ACCOUNTING_ERROR_CODE
)
VALUES
(
PN_AE_HEADERS_S.nextval,
l_EVENT_ID,
l_set_of_books_id ,
'PM EXPENSE' ,
'N',
l_period_name,
l_start_date,
'N',
-1,
l_header_desc,
pn_mo_cache_utils.get_current_org_id,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
SYSDATE,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.conc_request_id,
NULL
)
RETURNING AE_HEADER_ID INTO l_header_id;
pnp_debug_pkg.log('Inserting into lines for Expense');
INSERT INTO PN_AE_LINES_ALL
(
AE_LINE_ID ,
AE_HEADER_ID ,
AE_LINE_NUMBER ,
AE_LINE_TYPE_CODE ,
CODE_COMBINATION_ID ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
ENTERED_DR ,
ENTERED_CR ,
ACCOUNTED_DR ,
ACCOUNTED_CR ,
SOURCE_TABLE ,
SOURCE_ID ,
DESCRIPTION ,
ACCOUNTING_ERROR_CODE ,
ORG_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
PROGRAM_UPDATE_DATE ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
REQUEST_ID
)
VALUES
(
PN_AE_LINES_S.nextval,
L_HEADER_ID,
l_exp_number,
exp_acnt_tab(i).account_class,
exp_acnt_tab(i).account_id,
v_pn_export_currency_code,
l_conv_rate_type ,
v_pn_accounted_date,
v_pn_rate,
l_amt,
null,
l_accounted_amt,
null,
'PN_PAYMENT_ITEMS',
V_PN_PAYMENT_ITEM_ID,
l_line_desc,
NULL,
pn_mo_cache_utils.get_current_org_id,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
SYSDATE,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.conc_request_id
);
pnp_debug_pkg.log('Inserted into lines for Expense');
pnp_debug_pkg.log('Inserting into lines for Accrued Liability');
INSERT INTO PN_AE_LINES_ALL
(
AE_LINE_ID ,
AE_HEADER_ID ,
AE_LINE_NUMBER ,
AE_LINE_TYPE_CODE ,
CODE_COMBINATION_ID ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
ENTERED_DR ,
ENTERED_CR ,
ACCOUNTED_DR ,
ACCOUNTED_CR ,
SOURCE_TABLE ,
SOURCE_ID ,
DESCRIPTION ,
ACCOUNTING_ERROR_CODE ,
ORG_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
PROGRAM_UPDATE_DATE ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
REQUEST_ID
)
VALUES
(
PN_AE_LINES_S.nextval,
L_HEADER_ID,
l_acc_number,
acc_acnt_tab(i).account_class ,
acc_acnt_tab(i).account_id,
v_pn_export_currency_code,
l_conv_rate_type ,
v_pn_accounted_date,
v_pn_rate,
NULL,
l_amt,
NULL,
l_accounted_amt,
'PN_PAYMENT_ITEMS',
V_PN_PAYMENT_ITEM_ID,
l_line_desc,
NULL,
pn_mo_cache_utils.get_current_org_id,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
SYSDATE,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.conc_request_id
);
pnp_debug_pkg.log('Inserted into lines for Accrued Liability');
UPDATE pn_payment_items_all
SET transferred_to_ap_flag = 'Y' ,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
last_update_date = l_last_update_date
WHERE 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,
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 = V_PN_Payment_Schedule_Id;