[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT KH1.contract_number Investor_Agreement,
KH1.id Investor_Agreement_id,
KH1.pdt_id pdt_id,
KH1.currency_code,
KH1.currency_conversion_type,
KH1.currency_conversion_rate,
KH1.currency_conversion_date,
KH1.authoring_org_id org_id,
PARTY.name Investor_Name,
PARTY.description Investor_Description,
--PARTY.id1 Investor_Id,
TOP_LINE.cust_acct_id Investor_Id,
PARTY.id2 Investor_Id2,
TOP_LINE.ID TOP_LINE_ID,
TOP_KLE.AMOUNT,
TOP_LINE.START_DATE,
nvl(TOP_KLE.AMOUNT_STAKE,0) AMOUNT_STAKE
FROM
OKL_K_HEADERS_FULL_V KH1,
OKC_K_LINES_B TOP_LINE,
OKL_K_LINES TOP_KLE,
OKC_K_PARTY_ROLES_B PARTY_ROLE,
OKX_PARTIES_V PARTY,
OKC_LINE_STYLES_B LSEB,
OKC_STATUSES_V STS
WHERE
KH1.SCS_CODE = 'INVESTOR' AND
-- KH1.STS_CODE = 'ACTIVE' AND
KH1.id = p_inv_agr AND
TOP_LINE.dnz_chr_id = KH1.id AND
-- TOP_LINE.CLE_ID IS NULL AND
-- TOP_LINE.STS_CODE = 'ACTIVE' AND
TOP_KLE.ID = NVL(p_investor_line_id,TOP_KLE.ID) AND
TOP_KLE.ID = TOP_LINE.ID AND
PARTY_ROLE.cle_id = TOP_LINE.id AND
PARTY_ROLE.dnz_chr_id = TOP_LINE.dnz_chr_id AND
PARTY_ROLE.rle_code = 'INVESTOR' AND
PARTY_ROLE.jtot_object1_code = 'OKX_PARTY' AND
PARTY.id1 = PARTY_ROLE.object1_id1 AND
PARTY.id2 = PARTY_ROLE.object1_id2 AND
LSEB.ID = TOP_LINE.lse_id AND
LSEB.lty_code = 'INVESTMENT' AND
STS.CODE = TOP_LINE.sts_code;
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_top_line_id AND
rul.rule_information_category = 'BTO' AND
rgp.dnz_chr_id = p_invstr_agr_id;*/
SELECT B.cust_acct_site_id
FROM okc_k_lines_b A
,okx_cust_site_uses_v B
WHERE /*A.chr_id = p_invstr_agr_id
and A.cle_id IS NULL
and*/ A.id = p_top_line_id
and A.bill_to_site_use_id = B.id1;
SELECT cust_acct_site_id
FROM okx_cust_site_uses_v
WHERE id1 = p_id1 AND
PARTY_ID = p_party_id;*/
SELECT B.TERM_ID
FROM RA_TERMS_TL T, RA_TERMS_B B
WHERE B.TERM_ID = T.TERM_ID
and T.LANGUAGE = userenv('LANG')
and T.name = 'IMMEDIATE';
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
rul.rule_information_category = 'LAPMTH' AND
rgp.cle_id = p_top_line_id AND
rgp.dnz_chr_id = p_invstr_agr_id;
SELECT receipt_method_id
FROM okx_receipt_methods_v
WHERE id1 = p_id1 AND
customer_id = p_cust_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 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_top_line_id AND
rul.rule_information_category = 'LABACC' AND
rgp.dnz_chr_id = p_invstr_agr_id;
SELECT bank_account_id
FROM OKX_RCPT_METHOD_ACCOUNTS_V
WHERE id1 = p_id1;
SELECT ID1
FROM OKX_CUST_TRX_TYPES_V
WHERE name = 'Investor-OKL' AND
set_of_books_id = p_sob_id AND
org_id = p_org_id;
SELECT id
FROM okl_trx_types_tl
WHERE name = 'Billing'
AND LANGUAGE = 'US';
SELECT taxable_default_yn
FROM okl_strm_type_v
WHERE id = cp_sty_id;
SELECT
C.CODE_COMBINATION_ID,
C.AE_LINE_TYPE,
C.CRD_CODE,
C.ACCOUNT_BUILDER_YN,
C.PERCENTAGE
FROM OKL_AE_TEMPLATES A,
OKL_PRODUCTS_V B,
OKL_AE_TMPT_LNES C
WHERE A.aes_id = b.aes_id AND
A.start_date <= sysdate AND
(A.end_date IS NULL OR A.end_date >= sysdate) AND
A.memo_yn = 'N' AND
-- #4643924 added filter on special accounting code
NVL(A.FACTORING_SYND_FLAG,'INVESTOR') = 'INVESTOR' AND
NVL(A.INV_CODE, '-9999') = NVL(p_inv_code,'-9999') AND
b.id = p_pdt_id AND
a.sty_id = p_sty_id AND
a.try_id = p_try_id AND
C.avl_id = A.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 khr.STS_CODE
FROM okc_k_headers_all_b khr
WHERE khr.ID = p_khr_id
AND khr.SCS_CODE = 'INVESTOR';
select rul.rule_information1 investor_code
from okc_rule_groups_b rgp, okc_rules_b rul
where rgp.chr_id = rgp.dnz_chr_id
and rgp.dnz_chr_id = p_invstr_agr_id
and rgp.rgd_code = 'LASEAC'
and rul.dnz_chr_id = rgp.dnz_chr_id
and rul.rgp_id = rgp.id
and rul.rule_information_category = 'LASEAC';
INSERT INTO RA_INTERFACE_LINES (
ACCOUNTING_RULE_ID
,ACCOUNTING_RULE_DURATION
,AGREEMENT_ID
,AMOUNT
,BATCH_SOURCE_NAME
,COMMENTS
,CONVERSION_DATE
,CONVERSION_RATE
,CONVERSION_TYPE
,CREATED_BY
,CREATION_DATE
,CREDIT_METHOD_FOR_ACCT_RULE
,CREDIT_METHOD_FOR_INSTALLMENTS
,CURRENCY_CODE
,CUST_TRX_TYPE_ID
,DESCRIPTION
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LINE_TYPE
,TRX_NUMBER
,TRX_DATE
,GL_DATE
,PRINTING_OPTION
,CONS_BILLING_NUMBER
,INTERFACE_LINE_ATTRIBUTE1
,INTERFACE_LINE_ATTRIBUTE2
,INTERFACE_LINE_ATTRIBUTE3
,INTERFACE_LINE_ATTRIBUTE4
,INTERFACE_LINE_ATTRIBUTE5
,INTERFACE_LINE_ATTRIBUTE6
,INTERFACE_LINE_ATTRIBUTE7
,INTERFACE_LINE_ATTRIBUTE8
,INTERFACE_LINE_ATTRIBUTE9
,INTERFACE_LINE_ATTRIBUTE10
,INTERFACE_LINE_ATTRIBUTE11
,INTERFACE_LINE_ATTRIBUTE12
,INTERFACE_LINE_ATTRIBUTE13
,INTERFACE_LINE_ATTRIBUTE14
,INTERFACE_LINE_ATTRIBUTE15
-- ,INTERFACE_LINE_ID
,INTERFACE_LINE_CONTEXT
,INVENTORY_ITEM_ID
,INVOICING_RULE_ID
,ORIG_SYSTEM_BILL_CUSTOMER_ID
,ORIG_SYSTEM_BILL_ADDRESS_ID
,ORIG_SYSTEM_SHIP_CUSTOMER_ID
,ORIG_SYSTEM_SHIP_ADDRESS_ID
,ORIG_SYSTEM_BILL_CONTACT_ID
,ORIG_SYSTEM_SOLD_CUSTOMER_ID
,PRIMARY_SALESREP_NUMBER
,PRIMARY_SALESREP_ID
,PURCHASE_ORDER
,PURCHASE_ORDER_REVISION
,PURCHASE_ORDER_DATE
,CUSTOMER_BANK_ACCOUNT_ID
,RECEIPT_METHOD_ID
,RECEIPT_METHOD_NAME
,QUANTITY
,QUANTITY_ORDERED
,REASON_CODE
,REASON_CODE_MEANING
,REFERENCE_LINE_ID
,RULE_START_DATE
,SALES_ORDER
,SALES_ORDER_LINE
,SALES_ORDER_DATE
,SALES_ORDER_SOURCE
,SET_OF_BOOKS_ID
,TAX_EXEMPT_FLAG
,TAX_EXEMPT_NUMBER
,TAX_EXEMPT_REASON_CODE
,TERM_ID
,UNIT_SELLING_PRICE
,UNIT_STANDARD_PRICE
,UOM_CODE
,HEADER_Attribute_CATEGORY
,HEADER_Attribute1
,HEADER_Attribute2
,HEADER_Attribute3
,HEADER_Attribute4
,HEADER_Attribute5
,HEADER_Attribute6
,HEADER_Attribute7
,HEADER_Attribute8
,HEADER_Attribute9
,HEADER_Attribute10
,HEADER_Attribute11
,HEADER_Attribute12
,HEADER_Attribute13
,HEADER_Attribute14
,HEADER_Attribute15
,Attribute_CATEGORY
,Attribute1
,Attribute2
,Attribute3
,Attribute4
,Attribute5
,Attribute6
,Attribute7
,Attribute8
,Attribute9
,Attribute10
,Attribute11
,Attribute12
,Attribute13
,Attribute14
,Attribute15
,ORG_ID
,LEGAL_ENTITY_ID -- for LE Uptake project 08-11-2006
)
VALUES
( NULL
, NULL
, NULL
, invstr_rec.AMOUNT
,'OKL_INVESTOR'
, NULL
, l_currency_conversion_date
, DECODE(l_currency_conversion_type,'User',l_currency_conversion_rate,NULL)
, l_currency_conversion_type
, FND_global.user_id
, SYSDATE
, NULL
, NULL
, l_currency_code
, l_cust_trx_id --CUST_TRX_TYPE_ID
, SUBSTR(invstr_rec.Investor_Agreement||'-'||invstr_rec.Investor_Name||'-'||invstr_rec.AMOUNT,1,240)
, FND_global.user_id
, SYSDATE
, 'LINE' --r_ExtLine.LINE_TYPE
, NULL --TRX_NUMBER
, invstr_rec.START_DATE --TRX_DATE
, invstr_rec.START_DATE --TRX_DATE
, NULL
, NULL --XTRX_CONS_INVOICE_NUMBER
, SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Agreement)),1,30)
, SUBSTR(LTRIM(RTRIM(l_unique_id)),1,20)
, SUBSTR(LTRIM(RTRIM(l_unique_id)),21)
, SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Name)),1,30)
, 'INVESTOR RECEIVABLE' --SUBSTR(LTRIM(RTRIM(r_ExtLine.XTRX_CONS_LINE_NUMBER)),1,30)
, NULL --SUBSTR(LTRIM(RTRIM(r_ExtLine.XTRX_CONTRACT)),1,30)
, NULL --SUBSTR(LTRIM(RTRIM(r_ExtLine.XTRX_ASSET)),1,30)
, NULL --SUBSTR(LTRIM(RTRIM(r_ExtLine.XTRX_STREAM_GROUP)),1,30)
, NULL --SUBSTR(LTRIM(RTRIM(r_ExtLine.XTRX_STREAM_TYPE)),1,30)
, NULL --SUBSTR (r_ExtLine.XTRX_CONS_STREAM_ID, 1, 20)
, NULL --SUBSTR (r_ExtLine.XTRX_CONS_STREAM_ID, 21)
, NULL
, NULL
, NULL
, NULL
-- , r_ExtLine.ID
, 'OKL_INVESTOR'
, NULL
, NULL
, l_customer_id --CUSTOMER_ID
, l_cust_site_id --CUSTOMER_ADDRESS_ID
, l_customer_id --CUSTOMER_ID
, l_cust_site_id --NVL(l_ship_to , r_ExtHdr.CUSTOMER_ADDRESS_ID)
, NULL
, NULL
-- BEGIN bvaghela 032305 bug 4256274
, -3
, -3
-- END bvaghela 032305 bug 4256274
, NULL
, NULL
, NULL
, decode( l_how_created, 'MANUAL',NULL,l_bank_acct_id ) --CUSTOMER_BANK_ACCOUNT_ID
, l_receipt_method_id --RECEIPT_METHOD_ID
, NULL
, 1 --QUANTITY
, NULL
, NULL
, NULL
, NULL --REFERENCE_LINE_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, Okl_Accounting_Util.GET_SET_OF_BOOKS_ID
, decode(l_taxable_yn, 'Y', 'S', 'N', 'E', 'S')
, NULL
, decode(l_taxable_yn, 'Y', null, 'N', 'MANUFACTURER', null)
, l_terms
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, invstr_rec.ORG_ID
,l_legal_entity_id -- for LE Uptake project 08-11-2006
) ;
INSERT INTO RA_INTERFACE_SALESCREDITS_ALL (
INTERFACE_LINE_ATTRIBUTE1
,INTERFACE_LINE_ATTRIBUTE2
,INTERFACE_LINE_ATTRIBUTE3
,INTERFACE_LINE_ATTRIBUTE4
,INTERFACE_LINE_ATTRIBUTE5
,INTERFACE_LINE_ATTRIBUTE6
,INTERFACE_LINE_ATTRIBUTE7
,INTERFACE_LINE_ATTRIBUTE8
,INTERFACE_LINE_ATTRIBUTE9
,INTERFACE_LINE_ATTRIBUTE10
,INTERFACE_LINE_ATTRIBUTE11
,INTERFACE_LINE_ATTRIBUTE12
,INTERFACE_LINE_ATTRIBUTE13
,INTERFACE_LINE_ATTRIBUTE14
,INTERFACE_LINE_ATTRIBUTE15
,INTERFACE_LINE_CONTEXT
,SALES_CREDIT_AMOUNT_SPLIT
,SALES_CREDIT_PERCENT_SPLIT
,SALES_CREDIT_TYPE_ID
,SALES_CREDIT_TYPE_NAME
,SALESREP_ID
,SALESREP_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,ORG_ID
)
VALUES (
SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Agreement)),1,30)
, SUBSTR(LTRIM(RTRIM(l_unique_id)),1,20)
, SUBSTR(LTRIM(RTRIM(l_unique_id)),21)
, SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Name)),1,30)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'OKL_INVESTOR'
, NULL
, 100
, l_sales_type_credit
, 'Quota Sales Credit'
, -3
, -3
,l_user_id
,l_sysdate
,l_user_id
,l_sysdate
,invstr_rec.ORG_ID
);
INSERT INTO RA_INTERFACE_DISTRIBUTIONS
(ACCOUNT_CLASS
,AMOUNT
,PERCENT
,CODE_COMBINATION_ID
,INTERFACE_LINE_CONTEXT
,INTERFACE_LINE_ATTRIBUTE1
,INTERFACE_LINE_ATTRIBUTE2
,INTERFACE_LINE_ATTRIBUTE3
,INTERFACE_LINE_ATTRIBUTE4
,INTERFACE_LINE_ATTRIBUTE5
,INTERFACE_LINE_ATTRIBUTE6
,INTERFACE_LINE_ATTRIBUTE7
,INTERFACE_LINE_ATTRIBUTE8
,INTERFACE_LINE_ATTRIBUTE9
,INTERFACE_LINE_ATTRIBUTE10
,INTERFACE_LINE_ATTRIBUTE11
,INTERFACE_LINE_ATTRIBUTE12
,INTERFACE_LINE_ATTRIBUTE13
,INTERFACE_LINE_ATTRIBUTE14
,INTERFACE_LINE_ATTRIBUTE15
,ORG_ID
)
VALUES
( decode( dstrs_rec.CRD_CODE,'C','REV','REC') --l_account_class
, l_amount
, dstrs_rec.PERCENTAGE
, l_cc_id --r_ExtDistr.CODE_COMBINATION_ID
, 'OKL_INVESTOR'
, SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Agreement)),1,30) --SUBSTR ( r_ExtHdr.XTRX_INVOICE_PULL_YN,1,30 )
, SUBSTR(LTRIM(RTRIM(l_unique_id)),1,20) --SUBSTR (r_ExtHdr.XTRX_CONS_INVOICE_NUMBER,1,30 )
, SUBSTR(LTRIM(RTRIM(l_unique_id)),21) --SUBSTR ( r_ExtHdr.XTRX_FORMAT_TYPE,1,30 )
, SUBSTR(LTRIM(RTRIM(invstr_rec.Investor_Name)),1,30) -- SUBSTR ( r_ExtHdr.XTRX_PRIVATE_LABEL,1,30 )
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, invstr_rec.ORG_ID
) ;
SELECT id
FROM okl_trx_types_v
WHERE name = 'Billing';
-- Insert transaction header record
---------------------------------------------
Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,i_taiv_rec
,r_taiv_rec);
-- Insert transaction line record
---------------------------------------------
Okl_Txl_Ar_Inv_Lns_Pub.insert_txl_ar_inv_lns
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,i_tilv_rec
,r_tilv_rec);