[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT HOU.name
FROM HR_OPERATING_UNITS HOU
WHERE HOU.organization_id = p_org_id;
SELECT DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
mo_global.get_current_org_id()
FROM DUAL;
SELECT APPLICATION_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'OKL' ;
SELECT PAR.pay_site_id,
PV.vendor_name
FROM OKL_PARTY_PAYMENT_DTLS_V PAR,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL VS
WHERE PAR.cpl_id = p_party_id
AND PAR.pay_site_id = VS.vendor_site_id(+)
AND VS.vendor_id = PV.vendor_id;
SELECT ROL.rle_code party_role
FROM OKC_K_PARTY_ROLES_V ROL
WHERE ROL.id = p_party_id;
l_program_update_date DATE;
l_program_update_date,
l_org_id;
l_tapv_rec.program_update_date := l_program_update_date;
SELECT CLE.id,
CLE.name asset_number,
CHR.id khr_id,
CHR.contract_number contract_number,
CLE.start_date,
CLE.end_date,
CLE.date_terminated,
KLE.residual_value,
VPA.id vpa_id,
VPA.contract_number vpa_number,
KHR.pdt_id,
RET.retirement_id,
RET.date_retired,
CHR.scs_code -- rmunjulu 4622198
FROM OKC_K_LINES_V CLE,
OKL_K_LINES KLE,
OKC_K_HEADERS_B CHR,
OKL_K_HEADERS KHR,
OKC_LINE_STYLES_V LSE,
OKC_K_HEADERS_B VPA,
FA_RETIREMENTS RET,
OKX_ASSET_LINES_V OAL
WHERE CLE.id = KLE.id
AND KHR.id = CHR.id
AND KHR.id = CLE.chr_id
AND KHR.khr_id IS NOT NULL
AND KHR.khr_id = VPA.id -- contract has vendor program attached
AND CLE.lse_id = LSE.id
AND LSE.lty_code = 'FREE_FORM1' -- pick only financial assets
AND CLE.sts_code IN ('TERMINATED','EXPIRED') -- Asset should have been TERMINATED/EXPIRED
AND CLE.date_terminated <= TRUNC(p_date) -- Ended
AND OAL.parent_line_id = CLE.id
AND OAL.corporate_book IS NOT NULL
AND OAL.asset_id = RET.asset_id
-- AND RET.status = 'PROCESSED' -- Retirement transaction status is processed -- NOT REQUIRED
AND RET.book_type_code = OAL.corporate_book -- For corporate book
-- Asset is not Securitized is checked once the asset is obtained
AND CLE.id NOT IN (-- residual share already done for that retirement transaction
SELECT TAL.kle_id
FROM OKL_TRX_ASSETS TAS,
OKL_TXL_ASSETS_B TAL
WHERE TAL.kle_id = CLE.id
AND TAL.tas_id = TAS.id
AND TAS.tas_type = 'VRS'
AND TAL.tal_type = 'VRS'
AND TAS.tsu_code = 'PROCESSED'
AND TAL.retirement_id = RET.retirement_id -- do not pick if residual share already done for that retirement trn
)
AND 1 < ( -- has residual sharing with more than one party (if one party then it is Lessor)
SELECT count(rul.rule_information1)
FROM OKC_RULES_V rul,
OKC_RULE_GROUPS_B rgp
WHERE rul.dnz_chr_id = VPA.id
AND rul.rgp_id = rgp.id
AND rgp.rgd_code = 'VGLRS'
AND rul.rule_information_category = 'VGLRSP'
);
SELECT STM.id
FROM OKL_STREAMS_V STM,
OKL_STRM_TYPE_B STY
WHERE STM.kle_id = p_kle_id
AND STM.say_code = 'CURR'
AND STM.STY_ID = STY.ID
AND STY.ID = p_sty_id;
SELECT sysdate INTO l_sys_date FROM DUAL;
SELECT HOU.name
FROM HR_OPERATING_UNITS HOU
WHERE HOU.organization_id = p_org_id;
SELECT VPA.contract_number PROGRAM_AGREEMENT,
CLE.name ASSET_NUMBER,
CLE.item_description ASSET_DESCRIPTION,
CHR.contract_number CONTRACT_NUMBER,
CLE.date_terminated ASSET_TERMINATION_DATE,
PPD.vendor_name VENDOR,
TAL.fa_trx_date DISPOSITION_DATE,
--VPA.contract_number ||' '|| CHR.currency_code VENDOR_SHARE
TAL.residual_shr_amount ||' '|| CHR.currency_code VENDOR_SHARE
FROM OKC_K_LINES_V CLE,
OKC_K_HEADERS_B CHR,
OKC_K_HEADERS_B VPA,
OKL_K_HEADERS KHR,
OKL_TRX_ASSETS TAS,
OKL_TXL_ASSETS_B TAL,
OKL_PARTY_PAYMENT_DTLS_UV PPD
WHERE TAL.TAS_ID = TAS.ID
AND TAS.TAS_TYPE = 'VRS'
AND TAL.TAL_TYPE = 'VRS'
AND TAL.KLE_ID = CLE.ID
AND CLE.CHR_ID = CHR.ID
AND CHR.ID = KHR.ID
AND KHR.KHR_ID = VPA.ID
AND PPD.CPL_ID = TAL.RESIDUAL_SHR_PARTY_ID;
l_select_sql_1 VARCHAR2(3000);
l_select_sql_2 VARCHAR2(3000);
l_select_sql_1 := ' SELECT VPA.contract_number PROGRAM_AGREEMENT,CLE.name ASSET_NUMBER,CLE.item_description ASSET_DESCRIPTION,CHR.contract_number CONTRACT_NUMBER, ';
l_select_sql_2 := ' CLE.date_terminated ASSET_TERMINATION_DATE,PPD.vendor_name VENDOR, TAL.fa_trx_date, TAL.residual_shr_amount ||' || ''' ''' || '|| TAL.currency_code VENDOR_SHARE';
l_total_sql := l_select_sql_1 || l_select_sql_2 ||
l_from_sql_1 ||
l_where_sql_1 || l_where_sql_2;