[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select dnz_chr_id
from okc_k_lines_b
where id = p_kle_id;
SELECT cs.cust_acct_site_id, cp.standard_terms payment_term_id
FROM okc_k_headers_b khr
, okx_cust_site_uses_v cs
, okc_k_lines_b cle
, hz_customer_profiles cp
WHERE khr.id = p_khr_id
AND cle.dnz_chr_id = khr.id
AND cle.chr_id IS NOT NULL
AND cle.id = p_kle_id
AND cle.BILL_TO_SITE_USE_ID = cs.id1
AND khr.bill_to_site_use_id = cp.site_use_id(+)
UNION
SELECT cs.cust_acct_site_id, cp.standard_terms payment_term_id
FROM okc_k_headers_b khr
, okc_k_lines_b cle
, okc_k_items item
, okc_k_lines_b linked_asset
, okx_cust_site_uses_v cs
, hz_customer_profiles cp
WHERE khr.id = p_khr_id
AND cle.dnz_chr_id = khr.id
AND cle.id = p_kle_id
AND cle.chr_id IS NULL
AND cle.id = item.cle_id
AND item.object1_id1 = linked_asset.id
AND linked_asset.BILL_TO_SITE_USE_ID = cs.id1
AND khr.bill_to_site_use_id = cp.site_use_id(+);
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LABILL' AND
rgp.cle_id = p_kle_id AND
rul.rule_information_category = 'LAPMTH' AND
rgp.dnz_chr_id = p_khr_id
UNION
SELECT rul.object1_id1
FROM okc_k_lines_b cle
, okc_k_items_v item
, okc_k_lines_b linked_asset
, OKC_RULES_B rul
, Okc_rule_groups_B rgp
WHERE cle.dnz_chr_id = p_khr_id AND
cle.id = p_kle_id AND
cle.chr_id IS NULL AND
cle.id = item.cle_id AND
item.object1_id1 = linked_asset.id AND
linked_asset.id = rgp.cle_id AND
linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
rgp.rgd_code = 'LABILL' AND
rul.rgp_id = rgp.id AND
rul.rule_information_category = 'LAPMTH';
SELECT C.RECEIPT_METHOD_ID
FROM RA_CUST_RECEIPT_METHODS C
WHERE C.cust_receipt_method_id = p_cust_rct_mthd;
SELECT C.CREATION_METHOD_CODE
FROM AR_RECEIPT_METHODS M,
AR_RECEIPT_CLASSES C
WHERE M.RECEIPT_CLASS_ID = C.RECEIPT_CLASS_ID AND
M.receipt_method_id = p_rct_method_id;
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.cle_id = p_kle_id AND
rgp.rgd_code = 'LABILL' AND
rul.rule_information_category = 'LABACC' AND
rgp.dnz_chr_id = p_khr_id
UNION
SELECT rul.object1_id1
FROM okc_k_lines_b cle
, okc_k_items_v item
, okc_k_lines_b linked_asset
, OKC_RULES_B rul
, Okc_rule_groups_B rgp
WHERE cle.dnz_chr_id = p_khr_id AND
cle.id = p_kle_id AND
cle.chr_id IS NULL AND
cle.id = item.cle_id AND
item.object1_id1 = linked_asset.id AND
linked_asset.id = rgp.cle_id AND
linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
rgp.rgd_code = 'LABILL' AND
rul.rgp_id = rgp.id AND
rul.rule_information_category = 'LABACC';
SELECT bank_account_id
FROM OKX_RCPT_METHOD_ACCOUNTS_V
WHERE id1 = p_id1;
SELECT inf.contract_level_yn, -- Multi-contract Y/N
ity.group_by_contract_yn, -- Provide Contract Details
ity.group_asset_yn, -- Combine Assets
inf.name -- invoice group
FROM okl_invoice_formats_v inf,
okl_invoice_types_v ity,
okl_invc_line_types_v ilt,
okl_invc_frmt_strms_v frs,
okl_strm_type_v sty
WHERE inf.id = ity.inf_id
AND ity.inf_id = p_format_id
AND ilt.ity_id = ity.id
AND frs.ilt_id = ilt.id
AND sty.id = frs.sty_id
AND frs.sty_id = p_stream_id;
SELECT inf.contract_level_yn, -- Multi-contract Y/N
ity.group_by_contract_yn, -- Provide Contract Details
ity.group_asset_yn, -- Combine Assets
inf.name -- invoice group
FROM okl_invoice_formats_v inf,
okl_invoice_types_v ity,
okl_invc_line_types_v ilt
WHERE inf.id = p_format_id
AND ity.inf_id = inf.id
AND ilt.ity_id = ity.id
AND inf.ilt_id = ilt.id;
SELECT cr_dr_flag,
code_combination_id,
source_id,
amount,
percentage,
--Start code changes for rev rec by fmiao on 10/05/2004
NVL(comments, '-99') comments --End code changes for rev rec by fmiao on 10/05/2004
FROM okl_trns_acc_dstrs
WHERE source_id = p_source_id
AND source_table = p_source_table;
SELECT type
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_cust_trx_type_id;
SELECT cust_acct_id
FROM okc_k_headers_b
where contract_number = p_contract_number;
SELECT ipiu.payment_function
, fpc.payment_channel_code
, ipiu.payment_flow
FROM iby_pmt_instr_uses_all ipiu
,iby_fndcpt_pmt_chnnls_vl fpc
WHERE instrument_id = p_instr_use_id
AND fpc.instrument_type = ipiu.instrument_type;
SELECT cpr.object1_id1 party_id
, khr.authoring_org_id org_id
, khr.cust_acct_id
, khr.contract_number
, khr.application_id app_id
, khr.BILL_TO_SITE_USE_ID
, khr.CURRENCY_CODE
FROM OKL_K_HEADERS_FULL_V khr
, OKC_K_PARTY_ROLES_B CPR
WHERE khr.ID = cpr.chr_id
AND cpr.rle_code = 'LESSEE'
AND khr.ID = cp_khr_id;
SELECT rul.object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp,
Okc_rg_party_roles rpr,
Okc_k_party_roles_b cpl
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LAVENB' AND
rgp.dnz_chr_id = rgp.chr_id AND
rul.rule_information_category = 'LABACC' AND
rgp.dnz_chr_id = cp_khr_id AND
rpr.dnz_chr_id = rgp.dnz_chr_id AND
rpr.rgp_id = rgp.id AND
rpr.cpl_id = cpl.id AND
cpl.dnz_chr_id = rgp.chr_id AND
cpl.chr_id = rgp.chr_id AND
cpl.rle_code = 'OKL_VENDOR' AND
cpl.cust_acct_id = cp_cust_acct_id;
Select site_use_id
from HZ_CUST_SITE_USES
where cust_acct_site_id=cp_customer_address_id
and site_use_code = 'BILL_TO';
select a.cust_account_id, a.party_id
from hz_cust_acct_sites_all s,
hz_cust_accounts a
where s.cust_acct_site_id = cp_customer_address_id
AND s.cust_account_id = a.cust_account_id;
SELECT instrument_payment_use_id, fpc.payment_channel_code
FROM iby_pmt_instr_uses_all ibyinstr, iby_external_payers_all pay,iby_fndcpt_pmt_chnnls_vl fpc
WHERE ibyinstr.instrument_id = p_bank_id
AND ibyinstr.payment_flow = p_payment_flow
AND pay.payment_function = p_payment_function
AND ibyinstr.EXT_PMT_PARTY_ID = pay.EXT_PAYER_ID
AND pay.party_id = p_party_id
AND NVL(pay.org_id,p_org_id) = p_org_id
AND pay.cust_account_id = p_cust_acct_id
AND NVL(pay.acct_site_use_id,p_site_use_id) = p_site_use_id
AND fpc.instrument_type = ibyinstr.instrument_type;*/
SELECT instr_assignment_id,
fpc.payment_channel_code
FROM iby_fndcpt_payer_assgn_instr_v ibyinstr,
iby_fndcpt_pmt_chnnls_vl fpc
WHERE ibyinstr.instrument_id = p_bank_id
AND ibyinstr.payment_function = p_payment_function
AND ibyinstr.party_id = p_party_id
AND nvl(ibyinstr.org_id, p_org_id) = p_org_id
AND ibyinstr.cust_account_id = p_cust_acct_id
AND nvl(ibyinstr.acct_site_use_id, p_site_use_id) = p_site_use_id
AND nvl(assignment_end_date, sysdate + 10) > sysdate
AND fpc.instrument_type = ibyinstr.instrument_type
AND fpc.payment_channel_code in ('BANK_ACCT_XFER','CREDIT_CARD');
Select substrb(min(decode(acct_site_use_id, NULL, '2' || to_char(instr_assignment_id),
'1' || to_char(instr_assignment_id))), 2)
from IBY_FNDCPT_payer_assgn_instr_v
where instrument_type = 'BANKACCOUNT'
and cust_account_id = p_cust_acct_id
and p_site_use_id = nvl(acct_site_use_id,p_site_use_id)
and currency_code = p_currency_code
and order_of_preference = (select substrb(min(decode(acct_site_use_id, NULL, '2' || to_char(order_of_preference),
'1' || to_char(order_of_preference))), 2) from IBY_FNDCPT_payer_assgn_instr_v
where instrument_type = 'BANKACCOUNT'
and cust_account_id = p_cust_acct_id
and p_site_use_id = nvl(acct_site_use_id,p_site_use_id)
and currency_code = p_currency_code
and p_trx_date between NVL(assignment_start_date, p_trx_date)
and NVL(assignment_end_date, p_trx_date)
)
and p_trx_date between NVL(assignment_start_date,p_trx_date)
and NVL(assignment_end_date,p_trx_date);
select instrument_id
from iby_pmt_instr_uses_all
where instrument_payment_use_id = p_instr_payment_use_id;
SELECT rul.object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp,
Okc_rg_party_roles rpr,
Okc_k_party_roles_b cpl
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LAVENB' AND
rul.rule_information_category = 'LAPMTH' AND
rgp.dnz_chr_id = p_khr_id AND
rpr.dnz_chr_id = rgp.dnz_chr_id AND
rpr.rgp_id = rgp.id AND
rpr.cpl_id = cpl.id AND
cpl.dnz_chr_id = rgp.chr_id AND
cpl.chr_id = rgp.chr_id AND
cpl.rle_code = 'OKL_VENDOR' AND
cpl.cust_acct_id = p_cust_acct_id;
SELECT C.RECEIPT_METHOD_ID
FROM RA_CUST_RECEIPT_METHODS C
WHERE C.cust_receipt_method_id = p_payment_method_id;
SELECT C.CREATION_METHOD_CODE, M.PAYMENT_CHANNEL_CODE
FROM AR_RECEIPT_METHODS M,
AR_RECEIPT_CLASSES C
WHERE M.RECEIPT_CLASS_ID = C.RECEIPT_CLASS_ID AND
M.receipt_method_id = p_rct_method_id;
SELECT RM.Payment_channel_code,
CRM.CUST_RECEIPT_METHOD_ID,
CRM.RECEIPT_METHOD_ID
FROM RA_CUST_RECEIPT_METHODS CRM,
AR_RECEIPT_METHODS RM
WHERE RM.RECEIPT_METHOD_ID = CRM.RECEIPT_METHOD_ID
AND customer_id = p_cust_acct_id
AND NVL(site_use_id,-101) = NVL(p_site_use_id,-101)
AND NVL(CRM.PRIMARY_FLAG,'N') = 'Y'
AND NVL(TRUNC(CRM.END_DATE),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
SELECT *
FROM
(
SELECT * FROM
(SELECT PIU.INSTRUMENT_PAYMENT_USE_ID,
PIU.PAYMENT_FUNCTION,
PIU.ORDER_OF_PREFERENCE,
PIU.PAYMENT_FLOW
FROM IBY_PMT_INSTR_USES_ALL PIU,
IBY_CREDITCARD ccard,
iby_external_payers_all pay
WHERE ccard.INSTRID = PIU.instrument_id
AND PIU.INSTRUMENT_TYPE = p_instrument_type
AND sysdate <= NVL(ccard.inactive_date,sysdate)
AND PIU.EXT_PMT_PARTY_ID = pay.EXT_PAYER_ID
AND PAY.PARTY_ID = p_party_id
AND PAY.CUST_ACCOUNT_ID = p_cust_acct_id
AND NVL(PAY.ACCT_SITE_USE_ID,-101) = NVL(p_site_use_id,-101)
AND PIU.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND NVL(CCARD.EXPIRED_FLAG,'N') = 'N'
UNION
SELECT PIU.INSTRUMENT_PAYMENT_USE_ID,
PIU.PAYMENT_FUNCTION,
PIU.ORDER_OF_PREFERENCE,
PIU.PAYMENT_FLOW
FROM IBY_PMT_INSTR_USES_ALL PIU,
iby_ext_bank_accounts eb,
iby_external_payers_all pay
WHERE PIU.instrument_id = eb.ext_bank_account_id
AND PIU.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND PIU.INSTRUMENT_TYPE = p_instrument_type
AND PIU.EXT_PMT_PARTY_ID = pay.EXT_PAYER_ID
AND pay.party_id = p_party_id
AND PAY.CUST_ACCOUNT_ID = p_cust_acct_id
AND NVL(PAY.ACCT_SITE_USE_ID,-101) = NVL(p_site_use_id,-101)
AND sysdate <= NVL(eb.end_date,sysdate)
) ORDER BY ORDER_OF_PREFERENCE
)
WHERE ROWNUM = 1;
-- Call to insert the transaction extension through Payments PL/SQL API
IBY_FNDCPT_TRXN_PUB.CREATE_TRANSACTION_EXTENSION(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => l_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payer => l_payer,
p_payer_equivalency => l_payer_equivalency,
p_pmt_channel => l_pmt_channel,
p_instr_assignment => l_instrument_payment_use_id, --sosharma bug 6608452
p_trxn_attribs => l_trxn_attribs,
x_entity_id => l_entity_id,
x_response => l_response);
SELECT ipiu.payment_function
, fpc.payment_channel_code
, ipiu.payment_flow
FROM iby_pmt_instr_uses_all ipiu
, iby_fndcpt_pmt_chnnls_vl fpc
WHERE instrument_id = p_instr_use_id
AND fpc.instrument_type = ipiu.instrument_type;
SELECT cpr.object1_id1 party_id
, khr.authoring_org_id org_id
, khr.cust_acct_id
, khr.contract_number
, khr.application_id app_id
, khr.BILL_TO_SITE_USE_ID
, khr.CURRENCY_CODE
FROM OKL_K_HEADERS_FULL_V khr
, OKC_K_PARTY_ROLES_B CPR
WHERE khr.ID = cpr.chr_id
AND cpr.rle_code = 'LESSEE'
AND khr.ID = cp_khr_id;
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LABILL' AND
rgp.dnz_chr_id = rgp.chr_id AND
rul.rule_information_category = 'LABACC' AND
rgp.dnz_chr_id = cp_khr_id;
Select site_use_id
from HZ_CUST_SITE_USES
where cust_acct_site_id=cp_customer_address_id
and site_use_code = 'BILL_TO';
select a.cust_account_id, a.party_id
from hz_cust_acct_sites_all s,
hz_cust_accounts a
where s.cust_acct_site_id = cp_customer_address_id
AND s.cust_account_id = a.cust_account_id;
SELECT instrument_payment_use_id,fpc.payment_channel_code
FROM iby_pmt_instr_uses_all ibyinstr, iby_external_payers_all pay,iby_fndcpt_pmt_chnnls_vl fpc
WHERE ibyinstr.instrument_id = p_bank_id
AND ibyinstr.payment_flow = p_payment_flow
AND pay.payment_function = p_payment_function
AND ibyinstr.EXT_PMT_PARTY_ID = pay.EXT_PAYER_ID
AND pay.party_id = p_party_id
AND NVL(pay.org_id,p_org_id) = p_org_id
AND pay.cust_account_id = p_cust_acct_id
AND NVL(pay.acct_site_use_id,p_site_use_id) = p_site_use_id
AND fpc.instrument_type = ibyinstr.instrument_type;*/
SELECT instr_assignment_id,
fpc.payment_channel_code
FROM iby_fndcpt_payer_assgn_instr_v ibyinstr,
iby_fndcpt_pmt_chnnls_vl fpc
WHERE ibyinstr.instrument_id = p_bank_id
AND ibyinstr.payment_function = p_payment_function
AND ibyinstr.party_id = p_party_id
AND nvl(ibyinstr.org_id, p_org_id) = p_org_id
AND ibyinstr.cust_account_id = p_cust_acct_id
AND nvl(ibyinstr.acct_site_use_id, p_site_use_id) = p_site_use_id
AND nvl(assignment_end_date, sysdate + 10) > sysdate
AND fpc.instrument_type = ibyinstr.instrument_type
AND fpc.payment_channel_code in ('BANK_ACCT_XFER','CREDIT_CARD');
Select substrb(min(decode(acct_site_use_id, NULL, '2' || to_char(instr_assignment_id),
'1' || to_char(instr_assignment_id))), 2)
from IBY_FNDCPT_payer_assgn_instr_v
where instrument_type = 'BANKACCOUNT'
and cust_account_id = p_cust_acct_id
and p_site_use_id = nvl(acct_site_use_id,p_site_use_id)
and currency_code = p_currency_code
and order_of_preference = (select substrb(min(decode(acct_site_use_id, NULL, '2' || to_char(order_of_preference),
'1' || to_char(order_of_preference))), 2) from IBY_FNDCPT_payer_assgn_instr_v
where instrument_type = 'BANKACCOUNT'
and cust_account_id = p_cust_acct_id
and p_site_use_id = nvl(acct_site_use_id,p_site_use_id)
and currency_code = p_currency_code
and p_trx_date between NVL(assignment_start_date, p_trx_date)
and NVL(assignment_end_date, p_trx_date)
)
and p_trx_date between NVL(assignment_start_date,p_trx_date)
and NVL(assignment_end_date,p_trx_date);
select instrument_id
from iby_pmt_instr_uses_all
where instrument_payment_use_id = p_instr_payment_use_id;
-- Call to insert the transaction extension through Payments PL/SQL API
IBY_FNDCPT_TRXN_PUB.CREATE_TRANSACTION_EXTENSION(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => l_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payer => l_payer,
p_payer_equivalency => l_payer_equivalency,
p_pmt_channel => l_pmt_channel,
p_instr_assignment => l_instrument_payment_use_id, --sosharma bug 6608452
p_trxn_attribs => l_trxn_attribs,
x_entity_id => l_entity_id,
x_response => l_response);
select account_derivation
from okl_sys_acct_opts;
SELECT
TAI.ID TAI_ID
-- , TIL.AMOUNT AMOUNT
-- 19-Mar-2007 cklee -- Change amount referece to TLD instead |
, TLD.AMOUNT AMOUNT
, TIL.DESCRIPTION LINE_DESCRIPTION
, NVL(TLD.INVENTORY_ITEM_ID, TIL.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID
, TIL.inv_receiv_line_code LINE_TYPE
, TIL.QUANTITY
, TIL.LINE_NUMBER
, NVL(TLD.STY_ID, TIL.STY_ID) STY_ID
, KHR.ID KHR_ID
, KHR.CONTRACT_NUMBER
--start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
-- , KLE.NAME ASSET_NUMBER
, NULL ASSET_NUMBER
--end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
, TLD.INVOICE_FORMAT_LINE_TYPE -- STREAM_GROUP
, STY.NAME STREAM_TYPE
, TAI.CURRENCY_CODE
, TAI.currency_conversion_date
, TAI.currency_conversion_rate
, TAI.currency_conversion_type
, TAI.CUST_TRX_TYPE_ID
, TAI.IBT_ID CUSTOMER_ADDRESS_ID
-- , TAI.CUSTOMER_BANK_ACCOUNT_ID
, NVL(TIL.bank_acct_id, TAI.CUSTOMER_BANK_ACCOUNT_ID) CUSTOMER_BANK_ACCOUNT_ID
, TAI.IXX_ID CUSTOMER_ID
, TAI.DESCRIPTION HDR_DESCRIPTION
, NULL INVOICE_MESSAGE
, TAI.ORG_ID
, TAI.IRM_ID RECEIPT_METHOD_ID
, TAI.SET_OF_BOOKS_ID
, TAI.TAX_EXEMPT_FLAG
, TAI.IRT_ID TERM_ID
, TAI.DATE_INVOICED TRX_DATE
-- , TAI.TRX_NUMBER
--if auto-transaction generation is turn on, invoice_number (trx_number) is not a required column.
, NULL TRX_NUMBER -- refer to metalink: Note:277086.1
, TAI.CONSOLIDATED_INVOICE_NUMBER
, TLD.INVOICE_FORMAT_TYPE
, TAI.INVOICE_PULL_YN
, TAI.PRIVATE_LABEL
, TAI.LEGAL_ENTITY_ID -- for LE Uptake project 08-11-2006
, NULL ACCOUNT_CLASS
, NULL DIST_AMOUNT
, NULL DIST_PERCENT
, NULL CODE_COMBINATION_ID
-- , XLS.LSM_ID
, STY.ACCRUAL_YN rev_rec_basis
, NULL CM_ACCT_RULE
, TLD.TLD_ID_REVERSES rev_txn_id
-- , NULL REV_LSM_ID
, NVL(TLD.INVENTORY_ORG_ID, TIL.INVENTORY_ORG_ID) INVENTORY_ORG_ID
, KHR.inv_organization_id WARE_HOUSE_ID
, NVL(TLD.KLE_ID, TIL.KLE_ID) KLE_ID
, NULL SHIP_TO
, NULL l_inv_id
, NULL uom_code
, TLD.ID TXN_ID
--
-- R12 additional columns pass to AR interface
, TAI.OKL_SOURCE_BILLING_TRX
, TAI.Investor_Agreement_Number
, TAI.Investor_Name
, (select qte.quote_number from OKL_TRX_QUOTES_B qte where qte.id = TAI.QTE_ID) Quote_number
, NULL rbk_request_number
, TLD.RBK_ORI_INVOICE_NUMBER
, TLD.RBK_ORI_INVOICE_LINE_NUMBER
, TLD.RBK_ADJUSTMENT_DATE
, TAI.INF_ID
, TAI.TRY_ID
, TRYT.NAME TRY_NAME
-- bug 6744584: contingency fix, added contingecy_id..racheruv
, STY.CONTINGENCY_ID
, TLD.INVOICE_FORMAT_LINE_TYPE INVOICE_LINE_TYPE -- Bug 7045347
, TIL.ID TIL_ID -- Bug# 12617408
FROM OKL_TXD_AR_LN_DTLS_B TLD,
OKL_TXL_AR_INV_LNS_V TIL,
OKL_TRX_AR_INVOICES_V TAI,
OKC_K_HEADERS_ALL_B KHR,
--start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
-- OKC_K_LINES_V KLE,
--end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
OKL_STRM_TYPE_V STY,
OKL_TRX_TYPES_TL TRYT,
OKL_PARALLEL_PROCESSES OPP
WHERE TLD.STY_ID = STY.ID
AND TLD.TIL_ID_DETAILS = TIL.ID
AND TIL.TAI_ID = TAI.ID
AND TAI.KHR_ID = KHR.ID
--start:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
-- AND KLE.ID = TIL.KLE_ID
--end:| 06-Apr-2007 cklee -- Fixed kle_id, asset_number issues |
AND TAI.TRY_ID = TRYT.ID
AND TRYT.LANGUAGE = 'US'
AND TAI.TRX_STATUS_CODE = 'SUBMITTED'
AND OPP.OBJECT_TYPE = 'XTRX_CONTRACT'
AND OPP.OBJECT_VALUE = KHR.CONTRACT_NUMBER
AND OPP.ASSIGNED_PROCESS = p_assigned_process
ORDER BY TAI.ID
;
SELECT distinct khr.id khr_id
,khr.contract_number contract_number
,tai.id tai_id
,tai.okl_source_billing_trx
,tai.ixx_id customer_id
,iex_wf_ban_status_pub.get_turninvoff(hca.party_id,hca.cust_account_id,SYSDATE) bankruptcy_sts
FROM okl_trx_ar_invoices_b tai,
okc_k_headers_b khr,
hz_cust_accounts hca
WHERE tai.khr_id = khr.id
AND tai.ixx_id = khr.cust_acct_id
AND khr.cust_acct_id = hca.cust_account_id
AND hca.status = 'A'
AND khr.cust_acct_id IS NOT NULL
AND tai.trx_status_code = 'SUBMITTED';
SELECT khr.contract_number khr_number
,nvl(hca.account_name,hp.party_name) name
,COUNT(tai.id) cust_rec_cnt
FROM okl_trx_ar_invoices_b tai,
hz_cust_accounts hca,
okc_k_headers_b khr,
hz_parties hp
WHERE tai.ixx_id = khr.cust_acct_id
AND khr.cust_acct_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND tai.khr_id = khr.id
AND hca.status = 'A'
AND tai.trx_status_code = 'CUST-BANKRUPT'
GROUP BY khr.contract_number
,nvl(hca.account_name,hp.party_name);
SELECT COUNT(tai.id) cust_bkrpt_cnt
FROM okl_trx_ar_invoices_b tai
WHERE tai.trx_status_code = 'CUST-BANKRUPT';
SELECT distinct khr.id khr_id
,khr.contract_number contract_number
,tai.ixx_id vendor_customer_id
,iex_wf_ban_status_pub.get_turninvoff(hca.party_id,hca.cust_account_id,SYSDATE) bankruptcy_sts
FROM okl_trx_ar_invoices_b tai,
okc_k_headers_b khr,
hz_cust_accounts hca,
okc_k_party_roles_b opr
WHERE tai.khr_id = khr.id
AND khr.cust_acct_id <> tai.ixx_id
AND tai.ixx_id = hca.cust_account_id
AND opr.dnz_chr_id = khr.id
AND opr.cle_id IS NULL
AND opr.rle_code = 'OKL_VENDOR'
AND hca.status = 'A'
AND khr.cust_acct_id IS NOT NULL
AND tai.trx_status_code = 'SUBMITTED';
SELECT khr.contract_number khr_number
,ap.vendor_name name
,COUNT(tai.id) vndr_rec_cnt
FROM okl_trx_ar_invoices_b tai,
hz_cust_accounts hca,
okc_k_headers_b khr,
ap_suppliers ap,
hz_parties hp
WHERE tai.ixx_id = hca.cust_account_id
AND khr.cust_acct_id <> tai.ixx_id
AND tai.khr_id = khr.id
AND ap.party_id = hp.party_id
AND hp.party_id = hca.party_id
AND hca.status = 'A'
AND tai.trx_status_code = 'VNDR-BANKRUPT'
GROUP BY khr.contract_number
,ap.vendor_name;
SELECT COUNT(tai.id) vndr_bkrpt_cnt
FROM okl_trx_ar_invoices_b tai
WHERE tai.trx_status_code = 'VNDR-BANKRUPT';
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
FROM dual;
SELECT NAME
FROM hr_operating_units
WHERE organization_id = mo_global.get_current_org_id;
SELECT count(1)
FROM OKL_TXD_AR_LN_DTLS_B TLD
WHERE EXISTS
(SELECT 1
FROM OKL_TRX_AR_INVOICES_B TAI,
OKL_TXL_AR_INV_LNS_B TIL
WHERE TLD.TIL_ID_DETAILS = TIL.ID
AND TIL.TAI_ID = TAI.ID
AND TAI.trx_status_code = p_sts
AND TAI.request_id = p_req_id);
CURSOR tld_cnt_csr_selected( p_req_id NUMBER ) IS
SELECT count(1)
FROM OKL_TXD_AR_LN_DTLS_B TLD
WHERE EXISTS
(SELECT 1
FROM OKL_TRX_AR_INVOICES_B TAI,
OKL_TXL_AR_INV_LNS_B TIL
WHERE TLD.TIL_ID_DETAILS = TIL.ID
AND TIL.TAI_ID = TAI.ID
AND TAI.request_id = p_req_id);
l_selected_count NUMBER;
lx_last_updated_by okl_trx_ar_invoices_b.last_updated_by%TYPE := Fnd_Global.USER_ID;
lx_last_update_login okl_trx_ar_invoices_b.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
SELECT lease_inv_org_yn
FROM OKL_SYSTEM_PARAMS
WHERE org_id = cp_org_id;
SELECT SALESREP_ID, SALESREP_NUMBER
FROM ra_salesreps
--WHERE NAME = 'No Sales Credit';
SELECT contact.object1_id1
FROM okc_k_headers_b hdr, okc_contacts contact
WHERE contact.dnz_chr_id = hdr.id
AND hdr.contract_number = p_contract_number
AND contact.cro_code = 'SALESPERSON';
SELECT sales_credit_type_id
FROM so_sales_credit_types
WHERE name = 'Quota Sales Credit';
SELECT 1
FROM okc_k_lines_b kle
where kle.id = p_cle_id
and kle.cle_id is null; -- it's top line
select cle.id--, lse.lty_code
from okc_k_lines_b cle--,
-- okc_line_styles_b lse
-- where lse.id = cle.lse_id
-- and cle.cle_id is null
where cle.cle_id is null -- it's top line
start with cle.id = p_cle_id
connect by cle.id = prior cle.cle_id;
select cle.name
from OKC_K_LINES_V cle
where cle.id = p_cle_id;
SELECT c.OBJECT1_ID1
FROM okc_k_lines_b a,
okc_line_styles_b b,
okc_k_items c
WHERE a.cle_id = p_fin_asset_line_id
AND b.lty_code = 'ITEM'
AND a.lse_id = b.id
AND a.id = c.cle_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id;
SELECT --cim.object1_id1 item_instance,
--cim.object1_id2 "#",
csi.install_location_id
, csi.location_id
FROM csi_item_instances csi,
okc_k_items cim,
okc_k_lines_b inst,
okc_k_lines_b ib,
okc_line_styles_b lse
WHERE csi.instance_id = TO_NUMBER(cim.object1_id1)
AND cim.cle_id = ib.id
AND ib.cle_id = inst.id
AND inst.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM2'
AND inst.cle_id = p_kle_top_line;
SELECT a.CUST_ACCT_SITE_ID
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c
WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID AND
b.site_use_code = 'SHIP_TO' AND
a.party_site_id = c.party_site_id AND
a.cust_account_id = p_customer_num AND
a.org_id = p_org_id AND
c.party_site_id = p_install_location AND
c.location_id = p_location;
SELECT '1'
FROM okc_k_lines_v a,
okc_line_styles_v b
WHERE a.lse_id = b.id
AND b.lty_code = 'SOLD_SERVICE'
AND a.id = p_cle_id;
SELECT c.object1_id1
FROM okc_k_lines_v a,
okc_line_styles_v b,
okc_k_items c
WHERE a.lse_id = b.id
AND b.lty_code = 'SOLD_SERVICE'
AND a.id = p_cle_id
AND c.cle_id = a.id;
SELECT MEMO_LINE_ID
FROM ar_memo_lines
WHERE NAME = 'Lease Upfront Tax';
select lsm_id
from OKL_TXD_AR_LN_DTLS_B
where id = p_rev_txn_id;
select a.customer_trx_line_id
from ra_customer_trx_lines_all a
where a.INTERFACE_LINE_ATTRIBUTE14 = to_char(p_rev_txn_id); -- AKP
SELECT txs.trx_id,
txs.trx_line_id,
txs.trx_level_type,
txs.application_id,
txs.event_class_code,
txs.entity_code,
inv.tax_line_id -- Bug 6619311
FROM okl_tax_sources txs,
okl_txl_ar_inv_lns_b inv
,okl_txd_ar_ln_dtls_b tld
WHERE txs.trx_id = inv.txs_trx_id
AND trx_line_id = inv.txs_trx_line_id
AND inv.id = tld.til_id_details
AND inv.tai_id = p_tai_id
AND tld.id = p_tld_id;
SELECT HISTORICAL_FLAG,
TAX_REGIME_CODE,
TAX,
TAX_STATUS_CODE,
TAX_RATE_CODE,
TAX_JURISDICTION_CODE,
TAXABLE_AMT,
LEGAL_ENTITY_ID
FROM ZX_LINES
WHERE TAX_LINE_ID = p_zx_lines_id;
select id from okc_k_headers_all_b
where contract_number = p_contract_number;
SELECT 1
FROM okc_k_headers_b
WHERE id = p_chr_id
AND cust_acct_id = p_cust_acct_id;
SELECT C.CREATION_METHOD_CODE
FROM AR_RECEIPT_METHODS M,
AR_RECEIPT_CLASSES C
WHERE M.RECEIPT_CLASS_ID = C.RECEIPT_CLASS_ID AND
M.receipt_method_id = p_rct_method_id;
SELECT hsu.cust_acct_site_id
FROM okl_txl_ar_inv_lns_b til,
oe_order_lines_all oli,
oe_order_headers_all ohe,
hz_cust_site_uses_all hsu
WHERE til.id = p_til_id
AND oli.line_id = til.isl_id
AND ohe.header_id = oli.header_id
AND hsu.site_use_id = NVL(oli.ship_to_org_id, ohe.ship_to_org_id);
UPDATE okl_trx_ar_invoices_b trx
SET trx_status_code = 'CUST-BANKRUPT'
WHERE khr_id = cust_bankruptcy_rec.khr_id
AND ixx_id = cust_bankruptcy_rec.customer_id
AND trx_status_code = 'SUBMITTED';
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'VNDR-BANKRUPT'
WHERE khr_id = vndr_bankruptcy_rec.khr_id
AND ixx_id = vndr_bankruptcy_rec.vendor_customer_id
AND trx_status_code = 'SUBMITTED';
l_xfer_tbl.delete;
UPDATE okl_trx_ar_invoices_b
SET request_id = lx_request_id
WHERE ID = l_xfer_tbl(k).tai_id ;
inv_lines_tbl.delete;
inv_dist_tbl.delete;
sales_credits_tbl.delete;
ar_contingency_tbl.delete;
UPDATE okl_trx_ar_invoices_b
--end: | 15-FEB-07 cklee R12 Billing enhancement project
SET trx_status_code = 'PROCESSED',
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_application_id = lx_program_application_id,
program_id = lx_program_id
WHERE ID = l_hdr_id;
inv_lines_tbl(hdr_cnt).LAST_UPDATED_BY := G_user_id;
inv_lines_tbl(hdr_cnt).LAST_UPDATE_DATE := sysdate;
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').last_updated_by:'||inv_lines_tbl(hdr_cnt).last_updated_by);
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_lines_tbl('||hdr_cnt||').last_update_date:'||inv_lines_tbl(hdr_cnt).last_update_date);
sales_credits_tbl(sales_cr_cnt).LAST_UPDATED_BY := G_user_id;
sales_credits_tbl(sales_cr_cnt).LAST_UPDATE_DATE := sysdate;
sales_credits_tbl(sales_cr_cnt).LAST_UPDATED_BY := G_user_id;
sales_credits_tbl(sales_cr_cnt).LAST_UPDATE_DATE := sysdate;
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').last_update_date:' || sales_credits_tbl(sales_cr_cnt).last_update_date);
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','sales_credits_tbl('||sales_cr_cnt||').last_updated_by:' || sales_credits_tbl(sales_cr_cnt).last_updated_by);
SELECT AR_INTERFACE_CONTS_S.NEXTVAL
INTO ar_contingency_tbl(cont_cnt).INTERFACE_CONTINGENCY_ID
FROM DUAL;
ar_contingency_tbl(cont_cnt).LAST_UPDATED_BY := G_user_id;
ar_contingency_tbl(cont_cnt).LAST_UPDATE_DATE := sysdate;
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').last_updated_by:' || ar_contingency_tbl(cont_cnt).last_updated_by);
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','ar_contingency_tbl('||cont_cnt||').last_update_date:' || ar_contingency_tbl(cont_cnt).last_update_date);
inv_dist_tbl(n).LAST_UPDATED_BY := G_user_id;
inv_dist_tbl(n).LAST_UPDATE_DATE := sysdate;
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').last_update_date:' || inv_dist_tbl(n).last_update_date);
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','inv_dist_tbl('||n||').last_updated_by:' || inv_dist_tbl(n).last_updated_by);
UPDATE okl_trx_ar_invoices_b
--end: | 15-FEB-07 cklee R12 Billing enhancement project
SET trx_status_code = 'PROCESSED',
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_application_id = lx_program_application_id,
program_id = lx_program_id
WHERE ID = l_hdr_id;
error_tbl.delete;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into ra_interface_lines_all');
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Inserting into ra_interface_lines_all');
INSERT INTO ra_interface_lines_all
VALUES inv_lines_tbl(indx);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into ra_interface_lines_all');
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Done Inserting into ra_interface_lines_all');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into sales_credits');
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Inserting into sales_credits');
INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
VALUES sales_credits_tbl(indx);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into ar_contingency');
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Inserting into ar_contingency');
INSERT INTO AR_INTERFACE_CONTS_ALL
VALUES ar_contingency_tbl(indx);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into inv_dist');
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','Inserting into inv_dist');
INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL
VALUES inv_dist_tbl(indx);
update okl_trx_ar_invoices_b tai
set tai.trx_status_code = 'WORKING'
-- where id = l_error_xsi_id;
delete from RA_INTERFACE_SALESCREDITS
--start: | 15-FEB-07 cklee R12 Billing enhancement project
-- where interface_line_attribute10 = to_char(SUBSTR (error_tbl(indx).id,1, 20))
-- and interface_line_attribute11 = to_char(SUBSTR(error_tbl(indx).id,21));
delete from AR_INTERFACE_CONTS
--start: | 15-FEB-07 cklee R12 Billing enhancement project
-- where interface_line_attribute10 = to_char(SUBSTR (error_tbl(indx).id,1, 20))
-- and interface_line_attribute11 = to_char(SUBSTR(error_tbl(indx).id,21));
delete from RA_INTERFACE_DISTRIBUTIONS
--start: | 15-FEB-07 cklee R12 Billing enhancement project
-- where interface_line_attribute10 = to_char(SUBSTR (error_tbl(indx).id,1, 20))
-- and interface_line_attribute11 = to_char(SUBSTR(error_tbl(indx).id,21));
delete from ra_interface_lines
--start: | 15-FEB-07 cklee R12 Billing enhancement project
-- where interface_line_attribute10 = to_char(SUBSTR (error_tbl(indx).id,1, 20))
-- and interface_line_attribute11 = to_char(SUBSTR(error_tbl(indx).id,21));
l_xfer_tbl.delete;
DELETE FROM RA_INTERFACE_SALESCREDITS
WHERE interface_line_attribute14 IN
(SELECT TLD.ID
FROM OKL_TXD_AR_LN_DTLS_B TLD
, OKL_TXL_AR_INV_LNS_B TIL
, OKL_TRX_AR_INVOICES_B TAI
WHERE TIL.ID = TLD.TIL_ID_DETAILS
AND TAI.ID = TIL.TAI_ID
AND TAI.ID = l_error_tai_tbl(err_tai_cnt));
DELETE FROM AR_INTERFACE_CONTS
WHERE interface_line_attribute14 IN
(SELECT TLD.ID
FROM OKL_TXD_AR_LN_DTLS_B TLD
, OKL_TXL_AR_INV_LNS_B TIL
, OKL_TRX_AR_INVOICES_B TAI
WHERE TIL.ID = TLD.TIL_ID_DETAILS
AND TAI.ID = TIL.TAI_ID
AND TAI.ID = l_error_tai_tbl(err_tai_cnt));
DELETE FROM RA_INTERFACE_DISTRIBUTIONS
WHERE interface_line_attribute14 IN
(SELECT TLD.ID
FROM OKL_TXD_AR_LN_DTLS_B TLD
, OKL_TXL_AR_INV_LNS_B TIL
, OKL_TRX_AR_INVOICES_B TAI
WHERE TIL.ID = TLD.TIL_ID_DETAILS
AND TAI.ID = TIL.TAI_ID
AND TAI.ID = l_error_tai_tbl(err_tai_cnt));
DELETE FROM RA_INTERFACE_LINES
WHERE interface_line_attribute14 IN
(SELECT TLD.ID
FROM OKL_TXD_AR_LN_DTLS_B TLD
, OKL_TXL_AR_INV_LNS_B TIL
, OKL_TRX_AR_INVOICES_B TAI
WHERE TIL.ID = TLD.TIL_ID_DETAILS
AND TAI.ID = TIL.TAI_ID
AND TAI.ID = l_error_tai_tbl(err_tai_cnt));
UPDATE OKL_TRX_AR_INVOICES_B
SET TRX_STATUS_CODE = 'SUBMITTED'
WHERE ID = l_error_tai_tbl(err_tai_cnt);
OPEN tld_cnt_csr_selected( l_request_id);
FETCH tld_cnt_csr_selected INTO l_selected_count;
CLOSE tld_cnt_csr_selected;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Number of Invoice Lines Selected: '||l_selected_count);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Number of Invoice Lines Errored: '||(l_selected_count - l_succ_cnt));
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'SUBMITTED'
WHERE trx_status_code IN ('CUST-BANKRUPT','VNDR-BANKRUPT');
DELETE okl_parallel_processes
WHERE assigned_process = p_assigned_process;
DELETE okl_parallel_processes
WHERE assigned_process = p_assigned_process;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'INSERT Failed ' || SQLERRM);
fnd_log.STRING(fnd_log.level_statement,'okl.plsql.okl_arintf_pvt.get_rec_feeder.debug','INSERT Failed ' || SQLERRM);
DELETE okl_parallel_processes
WHERE assigned_process = p_assigned_process;