[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_v khr
, okx_cust_site_uses_v cs
, hz_customer_profiles cp
WHERE khr.id = p_khr_id
AND khr.bill_to_site_use_id = cs.id1
AND khr.bill_to_site_use_id = cp.site_use_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.dnz_chr_id = rgp.chr_id AND
rul.rule_information_category = 'LAPMTH' AND
rgp.dnz_chr_id = p_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.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 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 = p_khr_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 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 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 = ity.inf_id
AND ity.inf_id = p_format_id
AND ilt.ity_id = ity.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 cust_acct_id
FROM okc_k_headers_b
where contract_number = p_contract_number;
SELECT BATCH_SOURCE_ID
FROM ra_batch_sources_all
WHERE NAME = 'OKL_MANUAL'
AND START_DATE <= p_in_date
AND (END_DATE IS NULL OR p_in_date > END_DATE)
--gkhuntet staRT
AND ORG_ID = MO_GLOBAL.get_current_org_id();
SELECT kle_id
FROM OKL_CNSLD_AR_STRMS_V
WHERE id = p_lsm_id;
SELECT chr_id
FROM okc_k_lines_b
where id = p_cle_id;
SELECT FA.ID
FROM OKC_K_HEADERS_B CHR,
OKC_K_LINES_B TOP_CLE,
OKC_LINE_STYLES_b TOP_LSE,
OKC_K_LINES_B SUB_CLE,
OKC_LINE_STYLES_b SUB_LSE,
OKC_K_ITEMS CIM,
OKC_K_LINES_V FA,
OKC_LINE_STYLES_B AST_LSE,
OKL_CNSLD_AR_STRMS_B LSM
WHERE
CHR.ID = TOP_CLE.DNZ_CHR_ID AND
TOP_CLE.LSE_ID = TOP_LSE.ID AND
TOP_LSE.LTY_CODE IN('SOLD_SERVICE','FEE') AND
TOP_CLE.ID = SUB_CLE.CLE_ID AND
SUB_CLE.LSE_ID = SUB_LSE.ID AND
SUB_LSE.LTY_CODE IN ('LINK_SERV_ASSET', 'LINK_FEE_ASSET') AND
SUB_CLE.ID = LSM.KLE_ID AND
LSM.ID = p_lsm_id AND
CIM.CLE_ID = SUB_CLE.ID AND
CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST' AND
CIM.OBJECT1_ID1 = FA.ID AND
FA.LSE_ID = AST_LSE.ID AND
AST_LSE.LTY_CODE = 'FREE_FORM1';
SELECT name
FROM okc_k_lines_v
WHERE id = p_cle_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 decode(p_flg,'S','S','E','E','R','R','S')
FROM DUAL;
SELECT SALESREP_ID, NAME
FROM ra_salesreps
WHERE SALESREP_ID = p_salesrep_id;
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 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 kle_id
FROM OKL_CNSLD_AR_STRMS_V
WHERE id = p_lsm_id;
SELECT chr_id
FROM okc_k_lines_b
where id = p_cle_id;
SELECT FA.ID
FROM OKC_K_HEADERS_B CHR,
OKC_K_LINES_B TOP_CLE,
OKC_LINE_STYLES_b TOP_LSE,
OKC_K_LINES_B SUB_CLE,
OKC_LINE_STYLES_b SUB_LSE,
OKC_K_ITEMS CIM,
OKC_K_LINES_V FA,
OKC_LINE_STYLES_B AST_LSE,
OKL_CNSLD_AR_STRMS_B LSM
WHERE
CHR.ID = TOP_CLE.DNZ_CHR_ID AND
TOP_CLE.LSE_ID = TOP_LSE.ID AND
TOP_LSE.LTY_CODE IN('SOLD_SERVICE','FEE') AND
TOP_CLE.ID = SUB_CLE.CLE_ID AND
SUB_CLE.LSE_ID = SUB_LSE.ID AND
SUB_LSE.LTY_CODE IN ('LINK_SERV_ASSET', 'LINK_FEE_ASSET') AND
SUB_CLE.ID = LSM.KLE_ID AND
LSM.ID = p_lsm_id AND
CIM.CLE_ID = SUB_CLE.ID AND
CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST' AND
CIM.OBJECT1_ID1 = FA.ID AND
FA.LSE_ID = AST_LSE.ID AND
AST_LSE.LTY_CODE = 'FREE_FORM1';
SELECT name
FROM okc_k_lines_v
WHERE id = p_cle_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 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 decode(p_flg,'S','S','E','E','R','R','S')
FROM DUAL;
SELECT decode(p_flg,'E','MANUFACTURER',NULL)
FROM dual;
SELECT MEMO_LINE_ID
FROM ar_memo_lines
WHERE NAME = 'Lease Upfront Tax';
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 lease_inv_org_yn
FROM OKL_SYSTEM_PARAMS
WHERE org_id = cp_org_id;
SELECT *
FROM OKL_XTD_SELL_INVS_V
WHERE XLS_ID = p_xls_id;
l_trx_dist_tbl(i).LAST_UPDATED_BY := G_user_id;
l_trx_dist_tbl(i).LAST_UPDATE_DATE := sysdate;
SELECT DISTINCT c.contract_number
FROM okl_trx_csh_rcpt_all_b a, okl_txl_rcpt_apps_b b,
okc_k_headers_b c
WHERE a.FULLY_APPLIED_FLAG = 'N'
AND a.receipt_type = 'ADV'
AND a.id = b.rct_id_details
AND b.khr_id = c.id
AND c.sts_code IN ('BOOKED', 'EVERGREEN','TERMINATED')
AND c.contract_number = NVL(cp_cont_number ,c.contract_number)
--gkhuntet - FP Bug 5516814..end
--dkagrawa added union for ppd to to select contract number even if there is no advance receipt for contract
UNION
SELECT cp_cont_number
FROM dual
WHERE p_ppd_flow = 'Y';
SELECT lease_inv_org_yn
FROM OKL_SYSTEM_PARAMS
WHERE org_id = cp_org_id;
select account_derivation
from okl_sys_acct_opts;
SELECT
A.ID,
A.OBJECT_VERSION_NUMBER,
A.SFWT_FLAG,
A.ISI_ID,
A.TRX_DATE,
A.CUSTOMER_ID,
A.RECEIPT_METHOD_ID,
A.TERM_ID,
A.CURRENCY_CODE,
A.CURRENCY_CONVERSION_TYPE,
A.CURRENCY_CONVERSION_RATE,
A.CURRENCY_CONVERSION_DATE,
A.CUSTOMER_ADDRESS_ID,
A.SET_OF_BOOKS_ID,
A.RECEIVABLES_INVOICE_ID,
A.CUST_TRX_TYPE_ID,
A.INVOICE_MESSAGE,
A.DESCRIPTION,
A.XTRX_CONS_INVOICE_NUMBER,
A.XTRX_FORMAT_TYPE,
A.XTRX_PRIVATE_LABEL,
A.ATTRIBUTE_CATEGORY,
A.ATTRIBUTE1,
A.ATTRIBUTE2,
A.ATTRIBUTE3,
A.ATTRIBUTE4,
A.ATTRIBUTE5,
A.ATTRIBUTE6,
A.ATTRIBUTE7,
A.ATTRIBUTE8,
A.ATTRIBUTE9,
A.ATTRIBUTE10,
A.ATTRIBUTE11,
A.ATTRIBUTE12,
A.ATTRIBUTE13,
A.ATTRIBUTE14,
A.ATTRIBUTE15,
A.REFERENCE_LINE_ID,
A.TRX_NUMBER,
A.CUSTOMER_BANK_ACCOUNT_ID,
A.TAX_EXEMPT_FLAG,
A.TAX_EXEMPT_REASON_CODE,
A.XTRX_INVOICE_PULL_YN,
A.TRX_STATUS_CODE,
A.REQUEST_ID,
A.PROGRAM_APPLICATION_ID,
A.PROGRAM_ID,
A.PROGRAM_UPDATE_DATE,
A.ORG_ID,
A.CREATED_BY,
A.CREATION_DATE,
A.LAST_UPDATED_BY,
A.LAST_UPDATE_DATE,
A.LAST_UPDATE_LOGIN,
A.LEGAL_ENTITY_ID -- for LE Uptake project 08-11-2006
FROM Okl_Ext_Sell_Invs_V a,
Okl_Xtl_Sell_Invs_V b
WHERE a.ID = b.XSI_ID_DETAILS
AND xtrx_contract = p_contract_number
AND trx_status_code = 'ENTERED';
SELECT
B.ID,
B.OBJECT_VERSION_NUMBER,
B.SFWT_FLAG,
B.TLD_ID,
B.LSM_ID,
B.TIL_ID,
B.ILL_ID,
B.XSI_ID_DETAILS,
B.LINE_TYPE,
B.DESCRIPTION,
B.AMOUNT,
B.QUANTITY,
B.XTRX_CONS_LINE_NUMBER,
B.XTRX_CONTRACT,
B.XTRX_ASSET,
B.XTRX_STREAM_GROUP,
B.XTRX_STREAM_TYPE,
B.XTRX_CONS_STREAM_ID,
B.ISL_ID,
B.SEL_ID,
B.ATTRIBUTE_CATEGORY,
B.ATTRIBUTE1,
B.ATTRIBUTE2,
B.ATTRIBUTE3,
B.ATTRIBUTE4,
B.ATTRIBUTE5,
B.ATTRIBUTE6,
B.ATTRIBUTE7,
B.ATTRIBUTE8,
B.ATTRIBUTE9,
B.ATTRIBUTE10,
B.ATTRIBUTE11,
B.ATTRIBUTE12,
B.ATTRIBUTE13,
B.ATTRIBUTE14,
B.ATTRIBUTE15,
B.REQUEST_ID,
B.PROGRAM_APPLICATION_ID,
B.PROGRAM_ID,
B.PROGRAM_UPDATE_DATE,
B.ORG_ID,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN
FROM Okl_Xtl_Sell_Invs_V b
WHERE b.xsi_id_details = p_xsi_id;
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
-- start: bug 6744584 .. racheruv. get the contingency_id and pass to AR
-- for cash basis rev rec method on stream type.
, STY.CONTINGENCY_ID
-- end : bug 6744584 .. racheruv
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 TLD.receivables_invoice_id IS null
AND KHR.CONTRACT_NUMBER = p_contract_number
AND TAI.okl_source_billing_trx = 'STREAM' -- cklee 04/10/07 handle regular stream billing only
-- 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 trx_number
FROM ra_customer_trx_all
WHERE customer_trx_id = p_cust_trx_id;
SELECT *
FROM ar_trx_errors_gt
WHERE TRX_HEADER_ID = p_hdr_id;
SELECT SUM(NVL( extended_amount ,0))
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_cust_trx_id AND
LINE_TYPE = 'TAX';
SELECT cnr.id cnr_id, lln.id lln_id
FROM okl_cnsld_ar_hdrs_v cnr,
okl_cnsld_ar_lines_v lln,
okl_cnsld_ar_strms_v lsm
WHERE cnr.id = lln.cnr_id
AND lln.id = lsm.lln_id
AND lsm.id = p_lsm_id;
SELECT due_date
FROM ar_payment_schedules_all
WHERE customer_trx_id = p_cust_trx_id;
SELECT NVL(sty.accrual_yn, '1')
FROM okl_cnsld_ar_strms_v lsm
, okl_strm_type_v sty
WHERE lsm.id = p_lsm_id
AND lsm.sty_id = sty.id;
SELECT SALESREP_ID, SALESREP_NUMBER
FROM ra_salesreps
WHERE NAME = 'No Sales Credit';
SELECT sales_credit_type_id
FROM so_sales_credit_types
WHERE name = 'Quota Sales Credit';
SELECT AR_INTERFACE_CONTS_S.nextval
FROM DUAL;
SELECT error_message, invalid_value
FROM ar_trx_errors_gt;
SELECT TXS.TRX_BUSINESS_CATEGORY,
TTD.ID,
TTD.TAXABLE_AMT,
TTD.TAX_RATE_CODE,
TTD.TAX_AMT,
TTD.tax_rate_id
FROM OKL_TAX_SOURCES TXS,
OKL_TAX_TRX_DETAILS TTD
WHERE TXS.TRX_LINE_ID = p_trx_id
AND TTD.TXS_ID = TXS.ID;
r_xsiv_rec.PROGRAM_UPDATE_DATE := xsi_rec.PROGRAM_UPDATE_DATE;
r_xsiv_rec.LAST_UPDATED_BY := xsi_rec.LAST_UPDATED_BY;
r_xsiv_rec.LAST_UPDATE_DATE := xsi_rec.LAST_UPDATE_DATE;
r_xsiv_rec.LAST_UPDATE_LOGIN := xsi_rec.LAST_UPDATE_LOGIN;
r_xlsv_rec.PROGRAM_UPDATE_DATE := xls_rec.PROGRAM_UPDATE_DATE;
r_xlsv_rec.LAST_UPDATED_BY := xls_rec.LAST_UPDATED_BY;
r_xlsv_rec.LAST_UPDATE_DATE := xls_rec.LAST_UPDATE_DATE;
r_xlsv_rec.LAST_UPDATE_LOGIN := xls_rec.LAST_UPDATE_LOGIN;
SELECT count(*) INTO l_cnt
FROM ar_trx_errors_gt;
UPDATE Okl_Cnsld_Ar_Strms_b
SET RECEIVABLES_INVOICE_ID = l_customer_trx_id,
tax_amount = NVL(l_tax_amount,0)
WHERE ID = r_xlsv_rec.LSM_ID;
UPDATE Okl_Cnsld_Ar_Lines_B
SET TAX_AMOUNT = NVL(TAX_AMOUNT,0)+NVL(l_tax_amount,0)
WHERE ID = l_lln_id;
UPDATE Okl_Cnsld_Ar_Hdrs_B
SET amount = NVL(amount,0) + NVL(l_tax_amount,0),
due_date = l_due_date
WHERE ID = l_cnr_id;
UPDATE Okl_Ext_Sell_Invs_B
SET RECEIVABLES_INVOICE_ID = l_customer_trx_id,
TRX_STATUS_CODE = 'PROCESSED'
WHERE ID = r_xsiv_rec.id;
UPDATE Okl_Txl_Ar_Inv_Lns_B
SET RECEIVABLES_INVOICE_ID = l_customer_trx_id
WHERE id = xfer_rec.TIL_ID;
UPDATE Okl_Txd_Ar_Ln_Dtls_B
SET RECEIVABLES_INVOICE_ID = l_customer_trx_id
WHERE id = xfer_rec.TXN_ID;