[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT NVL(led.sla_description_language, USERENV('LANG')) language_code
FROM gl_ledgers led
,xla_ledger_options lopt
,gl_ledger_relationships led_rel
WHERE led.ledger_category_code in ('PRIMARY', 'SECONDARY')
AND led_rel.target_ledger_category_code in ('PRIMARY', 'SECONDARY')
AND led.ledger_id = lopt.ledger_id
AND lopt.enabled_flag = 'Y'
AND lopt.application_id = 540
AND led.ledger_id = led_rel.target_ledger_id
AND led_rel.primary_ledger_id = p_ledger_id;
SELECT htl.NAME org_name
FROM HR_ALL_ORGANIZATION_UNITS_TL htl
WHERE htl.organization_id = p_org_id
AND htl.LANGUAGE = p_ledger_lang;
SELECT ht.NAME org_name
FROM hr_all_organization_units ht
WHERE ht.organization_id = p_org_id;
SELECT khr.khr_id khr_khr_id
,chr.cust_acct_id cust_acct_id
,chr.contract_number contract_number
,chr.currency_code currency_code
,chr.START_DATE start_date
,chr.bill_to_site_use_id bill_to_site_use_id
,chr.orig_system_reference1 orig_system_reference1
,khr.assignable_yn assignable_yn
,chr.authoring_org_id authoring_org_id
,khr.converted_account_yn converted_account_yn
,khr.generate_accrual_override_yn generate_accrual_override_yn
,chr.cust_po_number cust_po_number
,chr.sts_code sts_code
,hr_org.NAME hr_org_name
,khr.pdt_id pdt_id
,chr.scs_code scs_code
FROM okc_k_headers_all_b chr
,okl_k_headers khr
,hr_all_organization_units hr_org
WHERE khr.id = l_khr_id
AND chr.id = khr.id
AND hr_org.organization_id = chr.authoring_org_id;
SELECT kpl.attribute_category
,kpl.attribute1
,kpl.attribute2
,kpl.attribute3
,kpl.attribute4
,kpl.attribute5
,kpl.attribute6
,kpl.attribute7
,kpl.attribute8
,kpl.attribute9
,kpl.attribute10
,kpl.attribute11
,kpl.attribute12
,kpl.attribute13
,kpl.attribute14
,kpl.attribute15
FROM okl_k_party_roles kpl
,okc_k_party_roles_b cplb
WHERE cplb.chr_id = l_khr_id
AND cplb.dnz_chr_id = cplb.chr_id
AND kpl.id = cplb.id
AND cplb.rle_code = 'LESSEE';
SELECT rrb_qve.value rev_rec_method_code
,icm_qve.value int_calc_method_code
FROM okl_pdt_qualitys icm_pqy
,okl_pdt_pqy_vals icm_pqv
,okl_pqy_values icm_qve
,okl_pdt_qualitys rrb_pqy
,okl_pdt_pqy_vals rrb_pqv
,okl_pqy_values rrb_qve
WHERE icm_pqv.pdt_id = l_pdt_id
AND icm_pqv.qve_id = icm_qve.id
AND icm_qve.pqy_id = icm_pqy.id
AND icm_pqy.name = 'INTEREST_CALCULATION_BASIS'
AND rrb_pqv.pdt_id = l_pdt_id
AND rrb_pqv.qve_id = rrb_qve.id
AND rrb_qve.pqy_id = rrb_pqy.id
AND rrb_pqy.name = 'REVENUE_RECOGNITION_METHOD';
SELECT
ia_chr.id ia_chr_id
,ia_chr.contract_number ia_contract_number
,ia_chr.START_DATE ia_start_date
,ia_pdt.NAME ia_product_name
FROM okl_pool_contents ia_pool_c
,okc_k_headers_all_b ia_chr
,okl_k_headers ia_khr
,okl_products ia_pdt
,okl_pools_all ia_pool
WHERE ia_pool_c.sty_code = p_sty_type
AND ia_pool_c.pol_id = ia_pool.id
AND ia_pool.khr_id = ia_chr.id
AND ia_chr.scs_code = G_INVESTOR
AND ia_chr.id = ia_khr.id
AND ia_pdt.id = ia_khr.pdt_id
AND ia_chr.id = ia_khr.id
AND ia_pool_c.khr_id = p_khr_id
AND ia_pool_c.status_code <> Okl_Pool_Pvt.G_POC_STS_PENDING ; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
SELECT
DECODE(ia_rule.dnz_chr_id,l_rent_ia_chr_id, ia_rule.rule_information1,NULL) rent_ia_accounting_code
,DECODE(ia_rule.dnz_chr_id,l_res_ia_chr_id,ia_rule.rule_information1,NULL) res_ia_accounting_code
FROM okc_rules_b ia_rule
,okc_rule_groups_b ia_rule_groups
WHERE ia_rule.rgp_id = ia_rule_groups.id
AND ia_rule.dnz_chr_id IN (l_rent_ia_chr_id, l_res_ia_chr_id)
AND ia_rule_groups.rgd_code = 'LASEAC'
AND ia_rule.rule_information_category = 'LASEAC';
SELECT cust_party.party_name
,cust_party.party_id
,cust_accounts.account_number
FROM okc_k_party_roles_b cust_party_roles
,hz_parties cust_party
,hz_cust_accounts cust_accounts
WHERE cust_party.party_type in ( 'PERSON','ORGANIZATION')
AND cust_party.party_id = cust_party_roles.object1_id1
AND '#' = cust_party_roles.object1_id2
AND cust_party_roles.jtot_object1_code = 'OKX_PARTY'
AND cust_party_roles.rle_code = 'LESSEE'
AND cust_party_roles.chr_id = l_khr_id
AND cust_party_roles.dnz_chr_id = l_khr_id
AND cust_accounts.cust_account_id = l_cust_acct_id;
SELECT ins.policy_number
,ins.ipe_code
FROM okl_ins_policies_all_b ins
WHERE ins.khr_id = l_khr_id
AND ins.ipy_type = 'LEASE_POLICY'
AND ins.quote_yn = 'N'
AND ins.iss_code = 'ACTIVE';
SELECT rep.name
FROM jtf_rs_salesreps_mo_v rep
WHERE salesrep_id = p_jtf_sales_rep_pk;
SELECT rep.name
FROM okc_contacts contact
,okx_salesreps_v rep
WHERE contact.DNZ_CHR_ID = l_khr_id
AND rep.id1 = contact.object1_id1
AND rep.id2 = contact.object1_id2;
SELECT chr_cr_master.scs_code CHR_TYPE
,decode(chr_cr_master.scs_code,'CREDITLINE_CONTRACT'
,chr_cr_master.contract_number, NULL) credit_line_number
,decode(chr_cr_master.scs_code,'MASTER_LEASE'
,chr_cr_master.contract_number, NULL) master_lease_number
,decode(chr_cr_master.scs_code,'MASTER_LEASE'
,chr_cr_master.id, TO_NUMBER(NULL)) master_lease_id
FROM okc_k_headers_all_b chr_cr_master
,okc_governances governances
WHERE governances.dnz_chr_id = l_khr_id
AND governances.chr_id_referred = chr_cr_master.ID
AND chr_cr_master.scs_code in ('CREDITLINE_CONTRACT', 'MASTER_LEASE');
SELECT crline_sub_chr.contract_number
FROM okc_k_headers_all_b crline_sub_chr
,okc_governances crline_gov
WHERE crline_gov.dnz_chr_id = l_mla_chr_id
AND crline_gov.chr_id_referred = crline_sub_chr.id
AND crline_sub_chr.scs_code = 'CREDITLINE_CONTRACT';
SELECT vp_chr.contract_number
FROM okc_k_headers_all_b vp_chr
,okl_k_headers khr
WHERE vp_chr.id = khr.khr_id
AND vp_chr.scs_code = 'PROGRAM'
AND khr.id = p_khr_id;
SELECT status.meaning status_meaning
FROM okc_statuses_tl status
WHERE status.code = p_sts_code
AND status.language = p_led_lang;
SELECT try_tl.NAME transaction_type_name
,try_b.trx_type_class trx_type_class_code
FROM okl_trx_types_tl try_tl
,okl_trx_types_b try_b
WHERE try_tl.id = p_try_id
AND TRY_B.ID = try_tl.id
AND try_tl.language = p_led_lang;
SELECT DISTINCT tal.dnz_khr_id khr_id
,kle_id kle_id
FROM okl_trx_assets tas
,okl_txl_assets_b tal
WHERE tal.tas_id = tas.id
AND tas.id = p_source_id;
SELECT tal.dnz_khr_id khr_id
,tal.kle_id kle_id
FROM okl_txl_assets_b tal
WHERE tal.id = p_source_id;
SELECT cleb.line_number
,kle.fee_type
,DECODE(cleb.lse_id,
33, clet.name, -- For FREE_FORM1 Asset
NULL) asset_number
,kle.date_delivery_expected
,decode(cleb.lse_id,
33, 'FREE_FORM1', -- Asset
1, 'SERVICE', -- Service
48, 'SERVICE',
52, 'FEE', -- Fee
NULL) contract_line_type
FROM okc_k_lines_b cleb
,okl_k_lines kle
,okc_k_lines_tl clet
WHERE cleb.id = p_kle_id
AND kle.id = cleb.id
AND cleb.id = clet.id
AND clet.language = USERENV('LANG');
SELECT pov.vendor_id
,pov.vendor_name
FROM okc_k_lines_b cleb_vendor
,po_vendors pov
,okc_k_party_roles_b pty
WHERE cleb_vendor.cle_id = p_kle_id
AND cleb_vendor.dnz_chr_id = p_khr_id
AND cleb_vendor.lse_id = 34
AND pty.cle_id = cleb_vendor.id
AND pty.rle_code = 'OKL_VENDOR'
AND pty.dnz_chr_id = p_khr_id
AND pov.vendor_id = pty.object1_id1;
SELECT hl.location_id installed_site_id
FROM hz_locations hl,
hz_party_sites hps,
csi_item_instances csi,
okc_k_items cim_ib,
okc_k_lines_b cle_ib,
okc_k_lines_b cle_inst,
okc_k_lines_b cle_fin
WHERE cle_fin.cle_id is null
AND cle_fin.chr_id = cle_fin.dnz_chr_id
AND cle_fin.lse_id = 33
AND cle_inst.cle_id = cle_fin.id
AND cle_inst.dnz_chr_id = cle_fin.dnz_chr_id
AND cle_inst.lse_id = 43
AND cle_ib.cle_id = cle_inst.id
AND cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
AND cle_ib.lse_id = 45
AND cim_ib.cle_id = cle_ib.id
AND cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
AND cim_ib.object1_id1 = csi.instance_id
AND cim_ib.object1_id2 = '#'
AND cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
AND csi.install_location_id = hps.party_site_id
AND csi.install_location_type_code = 'HZ_PARTY_SITES'
AND hps.location_id = hl.location_id
AND cle_fin.dnz_chr_id = p_khr_id
AND cle_fin.id = p_kle_id;
SELECT msitl.description description
,msitb.description b_description
FROM mtl_system_items_tl msitl
,mtl_system_items_b msitb
WHERE msitl.inventory_item_id = p_inventory_item_id_pk1
AND msitl.organization_id = p_inventory_org_id_pk2
AND msitl.inventory_item_id = msitb.inventory_item_id
AND msitl.organization_id = msitb.organization_id
AND msitl.LANGUAGE = p_ledger_language;
SELECT khr.inv_organization_id inv_organization_id
,hrb.NAME hrb_name
FROM okc_k_headers_all_b khr
,hr_all_organization_units hrb
WHERE khr.id = p_khr_id
AND hrb.organization_id = khr.inv_organization_id;
SELECT set_of_books_id ledger_id
FROM okl_sys_acct_opts_all sysop
,okc_k_headers_all_b cntrct
WHERE cntrct.id = p_khr_id
AND cntrct.authoring_org_id = sysop.org_id;
SELECT khr.attribute_category
,khr.attribute1
,khr.attribute2
,khr.attribute3
,khr.attribute4
,khr.attribute5
,khr.attribute6
,khr.attribute7
,khr.attribute8
,khr.attribute9
,khr.attribute10
,khr.attribute11
,khr.attribute12
,khr.attribute13
,khr.attribute14
,khr.attribute15
FROM okl_k_headers khr
WHERE khr.id = l_khr_id;
SELECT pdt.name pdt_name
,aes.name aes_name
,gts.name sgt_name
,gts.deal_type deal_type
,gts.tax_owner tax_owner
,gts.interest_calc_meth_code interest_calc_meth_code
,gts.revenue_recog_meth_code revenue_recog_meth_code
FROM okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
WHERE pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND pdt.id = p_pdt_id;
SELECT kle.attribute_category
,kle.attribute1
,kle.attribute2
,kle.attribute3
,kle.attribute4
,kle.attribute5
,kle.attribute6
,kle.attribute7
,kle.attribute8
,kle.attribute9
,kle.attribute10
,kle.attribute11
,kle.attribute12
,kle.attribute13
,kle.attribute14
,kle.attribute15
FROM okl_k_lines kle
WHERE kle.id = l_kle_id;
SELECT fac.category_id category_id
,fab.manufacturer_name manufacturer_name
,fab.model_number model_number
,fac.SEGMENT1 || '-' || fac.SEGMENT2 asset_category_name
FROM okc_k_lines_b cleb_fa
,fa_additions_b fab
,okc_k_items cim
,fa_categories fac
WHERE cleb_fa.cle_id = p_kle_id
AND cleb_fa.dnz_chr_id = p_khr_id
AND cleb_fa.lse_id = 42
AND cim.cle_id = cleb_fa.id
AND cim.dnz_chr_id = p_khr_id
AND cim.jtot_object1_code = 'OKX_ASSET'
AND fab.asset_id = cim.object1_id1
AND fac.category_id = fab.asset_category_id;
SELECT fdh.location_id
FROM okc_k_lines_b cleb_loc
,fa_distribution_history fdh
,okc_k_items cim
WHERE cleb_loc.cle_id = p_kle_id
AND cleb_loc.dnz_chr_id = p_khr_id
AND cleb_loc.lse_id = 42
AND cim.cle_id = cleb_loc.id
AND cim.dnz_chr_id = p_khr_id
AND fdh.asset_id = cim.object1_id1
AND cim.jtot_object1_code = 'OKX_ASSET'
AND fdh.transaction_header_id_out IS NULL;
SELECT location_id location_id
,loc.concatenated_segments asset_location_name
FROM fa_locations_kfv loc
WHERE loc.location_id = p_fdh_location_id;
SELECT tas.trans_number trans_number
,txl.id txl_id
,tas.id tas_id
FROM okl_trx_assets tas
,okl_txl_assets_b txl
WHERE tas.id = txl.tas_id and
txl.id = p_source_id;
SELECT tas.trans_number trans_number
,txl.id txl_id
,tas.id tas_id
FROM okl_trx_assets tas
,okl_txl_assets_b txl
,okl_txd_assets_b txd
WHERE tas.id = txl.tas_id and
txl.id = txd.tal_id and
txd.id = p_source_id;
SELECT txl_tl.description trans_line_description
FROM okl_txl_assets_tl txl_tl
WHERE txl_tl.id = p_txl_id
AND txl_tl.language = p_ledger_lang;
SELECT kle.Year_built year_of_manufacture
FROM okl_k_lines kle,
okc_k_lines_b cleb_year
WHERE kle.id = cleb_year.id
AND cleb_year.cle_id = p_kle_id
AND cleb_year.dnz_chr_id = p_khr_id
AND cleb_year.lse_id = 42;
SELECT ARB.BUCKET_NAME BUCKET_NAME
FROM OKL_BUCKETS OBKT
,AR_AGING_BUCKET_LINES_B ARBL
,AR_AGING_BUCKETS ARB
WHERE OBKT.IBC_ID = ARBL.AGING_BUCKET_LINE_ID
AND ARBL.AGING_BUCKET_ID = ARB.AGING_BUCKET_ID
AND OBKT.ID = p_bkt_id;
SELECT ctt.name trx_name
FROM ra_cust_trx_types ctt
WHERE ctt.cust_trx_type_id = p_trx_id;
SELECT date_effective_from
,quote_number
,qtp_code
FROM okl_trx_quotes_all_b
WHERE id = l_qte_id;
SELECT styb.code stream_type_code
,styb.stream_type_purpose stream_type_purpose
-- changed to contingency_name from styb.contingency
-- for bug fix 6744584. racheruv
,adr.contingency_name contingency_code
FROM okl_strm_type_b styb, ar_deferral_reasons adr
WHERE styb.id = p_sty_id
AND styb.contingency_id = adr.contingency_id(+);
SELECT stytl.NAME stream_type_name
FROM okl_strm_type_tl stytl
WHERE stytl.id = p_sty_id
AND stytl.language = p_lang;
SELECT invc.contract_number inv_agrmnt_number
,DECODE(invk.securitization_type,
'SALE', 'SECURITIZATION',
'LOAN', 'SYNDICATION',
'SECURITIZATION','SECURITIZATION',
'SYNDICATION', 'SYNDICATION') inv_agrmnt_synd_code
,pol.pool_number inv_agrmnt_pool_number
,invc.currency_code inv_agrmnt_currency_code
,invc.START_DATE inv_agrmnt_effective_from
,invc.sts_code inv_agrmnt_status_code
FROM okc_k_headers_all_b invc
,okl_k_headers invk
,okl_pools_all pol
WHERE invc.id = invk.id
AND invc.scs_code = G_INVESTOR
AND pol.khr_id = invk.id
AND invc.id = p_inv_agrmnt_id;
SELECT DISTINCT template_id
FROM okl_trns_acc_dstrs_all dist
WHERE source_table = p_source_table
AND source_id = p_source_id;
SELECT name
,memo_yn
FROM okl_ae_templates_all
WHERE id = p_template_id;
SELECT sub.NAME subsidy_name
,party.vendor_name subsidy_party_name
,party.vendor_id subsidy_vendor_id
FROM okl_subsidies_b sub
,okl_k_lines subsidy_line
,okc_k_party_roles_b role
,po_vendors party
WHERE sub.id = subsidy_line.subsidy_id
AND role.cle_id = subsidy_line.id
AND role.object1_id1 = party.vendor_id
AND subsidy_line.id = p_kle_id;
SELECT pdt.name pdt_name
FROM okl_products pdt
WHERE pdt.id = p_pdt_id;
SELECT lse.lty_code line_style
,cle.cle_id parent_line_id
FROM okc_k_lines_b cle
,okc_line_styles_b lse
WHERE cle.lse_id = lse.id
AND cle.id = p_kle_id;
SELECT tldl.id tldl_id
,tldl.description trans_line_description
FROM okl_txd_ar_ln_dtls_tl tldl
WHERE tldl.id = p_tld_tl_id
AND tldl.language = p_lang;
SELECT tpld.id tpld_id
,tpld.description trans_line_description
FROM okl_txl_ap_inv_lns_tl tpld
WHERE tpld.id = p_tpld_id
AND tpld.LANGUAGE = p_lang;
SELECT qte.quote_number quote_number
,qte.date_accepted quote_accepted_date
,qte.qtp_code quote_type
FROM okl_trx_quotes_all_b qte
,okl_txl_quote_lines_b tql
WHERE qte.id = tql.qte_id
AND qte.qst_code in ('ACCEPTED', 'COMPLETE')
AND qte.accepted_yn = 'Y'
AND tql.qlt_code = 'AMCFIA'
AND tql.kle_id = p_kle_id;
SELECT qte.quote_number quote_number
,qte.date_accepted quote_accepted_date
,qte.qtp_code quote_type
FROM okl_trx_quotes_all_b qte
,okl_txl_quote_lines_b tql
WHERE qte.id = tql.qte_id
AND qte.qst_code in ('ACCEPTED', 'COMPLETE')
AND qte.accepted_yn = 'Y'
AND tql.qlt_code = 'AMCFIA'
AND tql.kle_id = p_kle_id
AND EXISTS
(
SELECT 1
FROM okl_trx_assets tas
,okl_txl_assets_b tal
,okc_k_lines_b cleb
WHERE cleb.id = tql.kle_id
AND cleb.cle_id = tal.kle_id
AND tal.tas_id = tas.id
AND tas.id = p_split_asset_trx_id
);
SELECT qte.quote_number quote_number
,qte.date_accepted quote_accepted_date
,qte.qtp_code quote_type
FROM okl_trx_types_b trx_b
,okl_trx_contracts_all rel_trx
,okl_trx_quotes_all_b qte
WHERE trx_b.trx_type_class = 'RE_LEASE'
AND trx_b.id = rel_trx.try_id
AND rel_trx.khr_id_new = p_khr_id
AND rel_trx.tsu_code <> 'PROCESSED'
AND qte.id = rel_trx.qte_id;
SELECT o.ledger_id,
o.representation_code,
o.representation_name,
o.representation_type
FROM okl_representations_v o,
fa_book_controls f
WHERE o.ledger_id = f.set_of_books_id
AND f.book_type_name = p_book_type_name;
SELECT o.ledger_id,
o.representation_code,
o.representation_name,
o.representation_type
FROM okl_representations_v o,
fa_book_controls f
WHERE o.ledger_id = f.set_of_books_id
AND f.book_type_code = p_book_type_code;
SELECT reporting_pdt_id
FROM okl_products p
WHERE p.id = p_pdt_id;
SELECT book_type_name
FROM fa_book_controls
WHERE book_type_code = p_book_type_code;
SELECT tcn.khr_id khr_id
,tcn.qte_id qte_id
,tcn.pdt_id pdt_id
,tcn.set_of_books_id ledger_id
,try.accounting_event_class_code accounting_event_class_code
,try.id try_id
,tcn_type tcn_type
FROM okl_trx_contracts_all tcn
,okl_trx_types_b try
WHERE tcn.id = p_source_id
AND tcn.try_id = try.id
AND try.accounting_event_class_code IS NOT NULL;
SELECT tcl.kle_id
,tcl.khr_id
,tcl.id
,tcl.bkt_id
,tcl.sty_id
,tcn.set_of_books_id ledger_id
,try.accounting_event_class_code
FROM okl_txl_cntrct_lns_all tcl
,okl_trx_contracts_all tcn
,okl_trx_types_b try
WHERE tcl.id = p_source_id
AND tcl.tcn_id = tcn.id
AND try.id = tcn.try_id
AND try.accounting_event_class_code IS NOT NULL;
SELECT tcl.khr_id khr_id
,tcl.kle_id kle_id
,tcl.sty_id sty_id
,tcl.bkt_id bkt_id
,tcl.id tcl_id
FROM okl_txl_cntrct_lns_all tcl
WHERE tcl.tcn_id = p_tcn_id
ORDER BY kle_id, sty_id, bkt_id;
SELECT tcn.id tcn_id
,tcn.khr_id khr_id
,tcn.set_of_books_id ledger_id
,try.accounting_event_class_code accounting_event_class_code
FROM okl_trx_contracts_all tcn
,okl_trx_types_b try
WHERE tcn.id = p_tcn_id
AND tcn.try_id = try.id
AND try.accounting_event_class_code IS NOT NULL;
PROCEDURE delete_trx_extension(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_trans_hdr_rec IN tehv_rec_type
,x_trans_line_tbl OUT NOCOPY telv_tbl_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
-----------------------------------------------------------------
-- Declare Process Variable
-----------------------------------------------------------------
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRX_EXTENSION';
SELECT teh.header_extension_id header_extension_id
FROM OKL_TRX_EXTENSION_B teh
WHERE teh.source_id = p_trx_hdr_id
AND teh.source_table = p_trx_hdr_table;
SELECT tel.line_extension_id line_extension_id
,tel.source_id source_id
,tel.source_table source_table
FROM OKL_TXL_EXTENSION_B tel
WHERE tel.teh_id = p_teh_id;
'BEGIN API OKL_SLA_ACC_SOURCES_PVT.DELETE_TRX_EXTENSION');
'Extension Header ID To be Deleted=' || TO_CHAR(l_trans_hdr_rec.header_extension_id));
'Extension Line ID(s) To be Deleted=');
'Calling the okl_trx_extension_pvt.delete_trx_extension. HEADER_EXTENSION_ID = ' ||
TO_CHAR(l_trans_hdr_rec.header_extension_id) );
okl_trx_extension_pvt.delete_trx_extension(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_tehv_rec => l_trans_hdr_rec -- Initalized with the HEADER_EXTENSION_ID
);
'After executing the okl_trx_extension_pvt.delete_trx_extension l_return_status' || l_return_status );
'END OKL_SLA_ACC_SOURCES_PVT.DELETE_TRX_EXTENSION');
END delete_trx_extension;
PROCEDURE delete_fa_extension(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_fxhv_rec IN fxhv_rec_type
,x_fxlv_tbl OUT NOCOPY fxlv_tbl_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
-----------------------------------------------------------------
-- Declare Process Variable
-----------------------------------------------------------------
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRX_EXTENSION';
SELECT fxh.header_extension_id header_extension_id
FROM okl_ext_fa_header_sources_b fxh
WHERE fxh.source_id = p_source_id
AND fxh.source_table = p_source_table;
SELECT fxl.line_extension_id line_extension_id
,fxl.source_id source_id
,fxl.source_table source_table
FROM okl_ext_fa_line_sources_b fxl
WHERE fxl.header_extension_id = p_hdr_ext_id;
'BEGIN API OKL_SLA_ACC_SOURCES_PVT.DELETE_TRX_EXTENSION');
'Extension Header ID To be Deleted=' || TO_CHAR(l_fxhv_rec.header_extension_id));
'Extension Line ID(s) To be Deleted=');
'calling the okl_fa_extension_pvt okl_fxl_pvt.delete_row Line Count=' || l_fxlv_tbl.COUNT );
okl_fa_extension_pvt.delete_fxh_extension(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_fxhv_rec => l_fxhv_rec
);
'After okl_fa_extension_pvt.delete_fxh_extension: l_return_status ' || l_return_status );
'END OKL_SLA_ACC_SOURCES_PVT.DELETE_TRX_EXTENSION');
END delete_fa_extension;
SELECT hdr.header_extension_id header_extension_id
FROM okl_ext_ar_header_sources_b hdr
WHERE hdr.source_id = p_header_source_id
AND hdr.khr_id = p_khr_id;
SELECT tai.trx_number trans_number
,tai.tcn_id tai_tcn_id
FROM okl_trx_ar_invoices_b tai
WHERE tai.id = p_header_source_id;
SELECT tcn.qte_id qte_id
FROM okl_trx_contracts_all tcn
WHERE tcn.id = p_tcn_id;
SELECT tld.id source_id
,'OKL_TXD_AR_LN_DTLS_B' source_table
,tld.khr_id khr_id
,tld.kle_id kle_id
,tld.sty_id sty_id
,tai.try_id try_id
FROM okl_trx_ar_invoices_b tai
,okl_txl_ar_inv_lns_b til
,okl_txd_ar_ln_dtls_b tld
WHERE tld.til_id_details = til.id
AND til.tai_id = tai.id
AND tai.id = p_header_source_id;
SELECT adjl.id source_id
,'OKL_TXL_ADJSTS_LNS_B' source_table
,adjl.khr_id khr_id
,adjl.kle_id kle_id
,adjl.sty_id sty_id
,adj.try_id try_id
FROM okl_trx_ar_adjsts_all_b adj
,okl_txl_adjsts_lns_all_b adjl
WHERE adj.id = adjl.adj_id
AND adj.id = p_header_source_id;
PROCEDURE delete_ar_extension(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_rxhv_rec IN rxhv_rec_type
,x_rxlv_tbl OUT NOCOPY rxlv_tbl_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
-----------------------------------------------------------------
-- Declare Process Variable
-----------------------------------------------------------------
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRX_EXTENSION';
SELECT rxh.header_extension_id header_extension_id
FROM okl_ext_ar_header_sources_b rxh
WHERE rxh.source_id = p_source_id
AND rxh.source_table = p_source_table;
SELECT rxl.line_extension_id line_extension_id
,rxl.source_id source_id
,rxl.source_table source_table
FROM okl_ext_ar_line_sources_b rxl
WHERE rxl.header_extension_id = p_hdr_ext_id;
'BEGIN API OKL_SLA_ACC_SOURCES_PVT.DELETE_TRX_EXTENSION');
'Extension Header ID To be Deleted=' || TO_CHAR(l_rxhv_rec.header_extension_id));
'Extension Line ID(s) To be Deleted=');
'calling the okl_ar_extension_pvt.delete_rxh_extension Line Count=' || l_rxlv_tbl.COUNT );
okl_ar_extension_pvt.delete_rxh_extension(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_rxhv_rec => l_rxhv_rec
);
'After okl_ar_extension_pvt.delete_rxh_extension: l_return_status ' || l_return_status );
'END OKL_SLA_ACC_SOURCES_PVT.DELETE_TRX_EXTENSION');
END delete_ar_extension;
SELECT hdr.header_extension_id header_extension_id
FROM okl_ext_ap_header_sources_b hdr
WHERE hdr.source_id = p_header_source_id
AND hdr.khr_id = p_khr_id;
SELECT tap.vendor_invoice_number trans_number
FROM okl_trx_ap_invoices_b tap
WHERE tap.id = p_header_source_id;
SELECT tpl.id source_id
,'OKL_TXL_AP_INV_LNS_B' source_table
,tpl.khr_id khr_id
,tpl.kle_id kle_id
,tpl.sty_id sty_id
,tap.try_id try_id
,tap.vendor_invoice_number trans_number
FROM okl_trx_ap_invoices_b tap
,okl_txl_ap_inv_lns_b tpl
WHERE tap.id = tpl.tap_id
AND tap.id = p_header_source_id;
PROCEDURE delete_ap_extension(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_pxhv_rec IN pxhv_rec_type
,x_pxlv_tbl OUT NOCOPY pxlv_tbl_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
-----------------------------------------------------------------
-- Declare Process Variable
-----------------------------------------------------------------
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_AP_EXTENSION';
SELECT pxh.header_extension_id header_extension_id
FROM okl_ext_ap_header_sources_b pxh
WHERE pxh.source_id = p_source_id
AND pxh.source_table = p_source_table;
SELECT pxl.line_extension_id line_extension_id
,pxl.source_id source_id
,pxl.source_table source_table
FROM okl_ext_ap_line_sources_b pxl
WHERE pxl.header_extension_id = p_hdr_ext_id;
'BEGIN API OKL_SLA_ACC_SOURCES_PVT.DELETE_AP_EXTENSION');
'Extension Header ID To be Deleted=' || TO_CHAR(l_pxhv_rec.header_extension_id));
'Extension Line ID(s) To be Deleted=');
'calling the okl_ap_extension_pvt.delete_pxh_extension Line Count=' || l_pxlv_tbl.COUNT );
okl_ap_extension_pvt.delete_pxh_extension(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_pxhv_rec => l_pxhv_rec
);
'After okl_ap_extension_pvt.delete_pxh_extension: l_return_status ' || l_return_status );
'END OKL_SLA_ACC_SOURCES_PVT.DELETE_AP_EXTENSION');
END delete_ap_extension;
SELECT fa_dep.deprn_run_id deprn_run_id
,ast.dnz_chr_id khr_id
,ast.cle_id kle_id
,fa_dep.asset_id asset_id
FROM fa_deprn_summary fa_dep
,okc_k_items okc_item
,okc_k_lines_b ast
,okl_parallel_processes opp
WHERE fa_dep.book_type_code = p_book_type_code
AND fa_dep.period_counter = p_period_counter
AND fa_dep.deprn_source_code = 'DEPRN'
AND fa_dep.deprn_run_id IS NOT NULL
AND ( fa_dep.deprn_run_id > p_max_deprn_run_id OR
p_max_deprn_run_id IS NULL )
AND NVL(okc_item.object1_id2, '#') = '#'
AND okc_item.jtot_object1_code = 'OKX_ASSET'
AND okc_item.object1_id1 = fa_dep.asset_id
AND okc_item.cle_id = ast.id
AND ast.lse_id = 42 -- FIXED_ASSET Line Style ID
AND opp.assigned_process = p_worker_id -- Fetch only Current Workers Contracts
AND ast.dnz_chr_id = opp.khr_id
ORDER BY fa_dep.deprn_run_id, ast.dnz_chr_id, ast.cle_id
; -- End of Cursor: get_okl_assets_csr
SELECT MAX(fa_transaction_id) max_deprn_run_id
FROM okl_ext_fa_line_sources_b fxl
WHERE fxl.source_table = 'FA_DEPRN_SUMMARY'
AND fxl.asset_book_type_code = p_asset_book_type_code
AND fxl.period_counter = p_period_counter
; -- End of Cursor get_max_deprn_run_id
l_deprn_asset_tbl.DELETE;
l_deprn_asset_tbl.DELETE;
DELETE OKL_PARALLEL_PROCESSES
WHERE khr_id = l_khr_id_tbl(khr_index);
SELECT chr.id khr_id
,chr.contract_number contract_number
,COUNT(fa_dep.asset_id) no_of_assets
FROM fa_deprn_summary fa_dep
,fa_books fa_books
,okc_k_headers_all_b chr
WHERE -- Predicates on fa_deprn_summary
fa_dep.book_type_code = p_book_type_code
AND fa_dep.period_counter = p_period_counter
AND fa_dep.deprn_source_code = 'DEPRN'
AND fa_dep.deprn_run_id IS NOT NULL
AND ( fa_dep.deprn_run_id > p_max_deprn_run_id OR
p_max_deprn_run_id IS NULL )
-- Predicates on fa_books
AND fa_books.transaction_header_id_out IS NULL
AND fa_books.date_ineffective IS NULL
AND fa_books.contract_id IS NOT NULL
-- Join Conditions between fa_deprn_summary and fa_books
AND fa_dep.asset_id = fa_books.asset_id
AND fa_dep.book_type_code = fa_books.book_type_code
-- Join conditions between fa_books and okc_k_headers_all_b
AND fa_books.contract_id = chr.id
GROUP BY chr.id, CHR.contract_number
ORDER BY COUNT(fa_dep.asset_id) DESC;
SELECT MAX(fa_transaction_id) max_deprn_run_id
FROM okl_ext_fa_line_sources_b fxl
WHERE fxl.source_table = 'FA_DEPRN_SUMMARY'
AND fxl.asset_book_type_code = p_asset_book_type_code
AND fxl.period_counter = p_period_counter
; -- End of Cursor get_max_deprn_run_id
SELECT okl_opp_seq.NEXTVAL
INTO l_seq_next
FROM DUAL;
l_temp_deprn_contracts_tbl.DELETE;
log_msg(FND_FILE.LOG, 'Before calling the Bulk Insert into the OKL_PARALLEL_PROCESSES' );
INSERT INTO OKL_PARALLEL_PROCESSES (
OBJECT_TYPE
,OBJECT_VALUE
,ASSIGNED_PROCESS
,PROCESS_STATUS
,CREATION_DATE
,KHR_ID
,VOLUME
)
VALUES (
G_OBJECT_TYPE_DEP_KHR -- Object Type
,l_object_value_tbl(dep_index) -- Object Value
,l_assigned_process_tbl(dep_index) -- Assigned Process
,'PENDING_ASSIGNMENT' -- Process Status
,SYSDATE -- Creation Date
,l_khr_id_tbl(dep_index) -- KHR_ID
,l_volume_tbl(dep_index) -- Volume
);
log_msg(FND_FILE.LOG, 'After calling the Bulk Insert into the OKL_PARALLEL_PROCESSES' );
log_msg(FND_FILE.LOG, 'Committed the Insertion of the OKL_PARALLEL_PROCESSES Records' );
UPDATE OKL_PARALLEL_PROCESSES
SET assigned_process = l_seq_next || '-' || l_assigned_process_tbl(dep_index)
,process_status = 'ASSIGNED'
WHERE object_type = G_OBJECT_TYPE_DEP_KHR
AND object_value = l_object_value_tbl(dep_index)
AND process_status = 'PENDING_ASSIGNMENT'
AND khr_id = l_khr_id_tbl(dep_index);
log_msg(FND_FILE.LOG, 'Updated the Records in OKL_PARALLEL_PROCESSES with the Assigned Process' );