The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Authoring_Org_Id FROM OKC_K_HEADERS_B
WHERE id = p_chrid;
SELECT uom_code FROM okc_time_code_units_b
WHERE tce_code = 'MONTH'
AND quantity = 1;
SELECT Org_Id FROM OKC_K_HEADERS_B
WHERE id = p_chrid;
SELECT period_set_name FROM gl_sets_of_books
WHERE set_of_books_id = p_gl_set_of_books_id;
Cursor l_hdrs_csr Is Select Org_Id From OKC_K_HEADERS_B
Where id = p_chrid;
SELECT OI2.ORG_INFORMATION3 set_of_books_id
FROM
HR_ORGANIZATION_INFORMATION OI2,
HR_ORGANIZATION_INFORMATION OI1,
HR_ALL_ORGANIZATION_UNITS OU
WHERE oi1.organization_id = ou.organization_id
AND oi2.organization_id = ou.organization_id
AND oi1.org_information_context = 'CLASS'
AND oi1.org_information1 = 'OPERATING_UNIT'
AND oi2.org_information_context = 'Operating Unit Information'
AND ou.organization_id = p_org_id;
/* Above select avoids OKX view usage
SELECT set_of_books_id From OKX_ORGANIZATION_DEFS_V
WHERE organization_type = 'OPERATING_UNIT'
AND information_type = 'Operating Unit Information'
AND organization_id = p_org_id;
SELECT count(lvl.id) tot_no_of_lvl,sub.nos no_of_lvl
from oks_level_elements lvl,
(select count(*) nos
from oks_level_elements lev
where lev.cle_id = p_cle_id
and trunc(lev.date_start) <= trunc(p_date)
) sub
WHERE lvl.cle_id = p_cle_id
GROUP BY sub.nos;
Cursor l_get_cov_lvl(p_cle_id IN NUMBER) IS SELECT id from okc_k_lines_b WHERE cle_id = p_cle_id AND lse_id in (18,25); Cursor Cur_billinstance_hdr (p_date_billed_from IN DATE, p_date_billed_to IN DATE,
SELECT c.Customer_trx_line_id,
d.trx_date,
d.exchange_rate_type,d.exchange_date, d.exchange_rate
FROM OKS_BILL_CONT_LINES a
, oks_bill_txn_lines b
, ra_customer_trx_all d --Okx_customer_trx_v d
, ra_customer_trx_lines_all c --Okx_cust_trx_lines_v c
WHERE a.date_billed_to = p_date_billed_to -- Bcl_rec.date_billed_to
AND a.cle_id = p_id -- Bcl_rec.cle_id
AND a.id = b.bcl_id
AND a.bill_action = 'RI'
AND c.sales_order = p_contract_number||
decode(p_con_modifier,null,'','-'||p_con_modifier)
AND c.interface_line_attribute1 = p_contract_number
AND nvl(c.interface_line_attribute2,'-') = nvl(p_con_modifier ,'-')
AND c.Interface_line_attribute3 = b.bill_instance_number
AND c.Interface_line_context = 'OKS CONTRACTS'
AND c.customer_trx_id = d.customer_trx_id
ORDER BY c.extended_amount ;
SELECT c.Customer_trx_line_id,
--c.customer_trx_id,
d.trx_date,
d.exchange_rate_type,d.exchange_date, d.exchange_rate
FROM ra_customer_trx_all d, --Okx_customer_trx_v d
ra_customer_trx_lines_all c --Okx_cust_trx_lines_v c
WHERE c.interface_line_attribute1 = p_contract_number
AND nvl(c.interface_line_attribute2,'-') = nvl(p_contract_modifier ,'-')
AND c.Interface_line_attribute3 = to_char(p_bill_instance_number)
AND c.sales_order = p_contract_number||
decode(p_contract_modifier,null,'','-'||p_contract_modifier)
AND c.Interface_line_context = 'OKS CONTRACTS'
AND c.customer_trx_id = d.customer_trx_id
ORDER BY c.extended_amount ;
SELECT c.Customer_trx_line_id,
--c.customer_trx_id,
d.trx_date,
d.exchange_rate_type,d.exchange_date, d.exchange_rate
FROM OKS_BILL_SUB_LINES a
, oks_bill_cont_lines e
, oks_bill_txn_lines b
, ra_customer_trx_all d
, ra_customer_trx_lines_all c
WHERE a.DATE_Billed_to = p_date_to -- Bsl_rec.date_billed_to
AND a.cle_id = p_id -- Bsl_rec.cle_id
AND a.id = b.bsl_id
AND a.bcl_id = e.id
AND e.bill_action = 'RI'
AND c.Interface_line_Attribute1 = p_contract_number
AND nvl(c.interface_line_attribute2,'-') = nvl(p_contract_modifier ,'-')
AND c.Interface_line_attribute3 = b.bill_instance_number
AND c.sales_order = p_contract_number||
decode(p_contract_modifier,null,'','-'||p_contract_modifier)
AND c.Interface_line_context = 'OKS CONTRACTS'
AND c.customer_trx_id = d.customer_trx_id
ORDER BY c.extended_amount ;
SELECT average from oks_bill_sub_lines
WHERE bcl_id = p_bcl_id;
SELECT object1_id1
FROM okc_k_rel_objs
WHERE cle_id = p_cle_id;
SELECT txl.customer_trx_line_id ,txh.trx_date trx_date,
txh.exchange_rate_type,txh.exchange_date, txh.exchange_rate
FROM ra_customer_trx_all txh ,
ra_customer_trx_lines_all txl ,
mtl_system_items mtl
WHERE txl.interface_line_attribute6 = to_char(p_line_id)
AND txl.interface_line_context = 'ORDER ENTRY'
AND txh.customer_trx_id = txl.customer_trx_id
AND txl.inventory_item_id = mtl.inventory_item_id
AND mtl.service_item_flag = 'Y';
SELECT rl.Customer_trx_line_id
FROM ra_customer_trx_lines_all rl
WHERE rl.customer_trx_id = p_comm_id;
Select decode(p_type,'INV',Cust_trx_type_id,'CM',credit_memo_type_id),
post_to_gl
From RA_CUST_TRX_TYPES_ALL
Where SET_OF_BOOKS_ID = p_id
And org_id = p_org_id
And Cust_trx_type_id = p_object1_id1;
Select /*+ PARALLEL(a) */
decode(p_type,'INV',Cust_trx_type_id,'CM',credit_memo_type_id),
post_to_gl
From RA_CUST_TRX_TYPES_ALL a
Where a.SET_OF_BOOKS_ID = p_id
And a.org_id = p_org_id
And a.type = 'INV'
And a.name = 'Invoice-OKS';
SELECT party_id
FROM hz_cust_accounts_all
WHERE cust_account_id = p_cust_account
AND nvl(org_id,p_org_id) = p_org_id;
SELECT hz.party_id from hz_party_sites hz
where hz.party_site_id in (
SELECT site.party_site_id from hz_cust_acct_sites_all site
where site.cust_acct_site_id in
( select uses.cust_acct_site_id
from hz_cust_site_uses_all uses
where site_use_id = p_bill_to_site_use_id
and site_use_code = 'BILL_TO')
and nvl(site.org_id,p_org_id) = p_org_id);
SELECT rl.Customer_trx_line_id
FROM ra_customer_trx_lines_all rl
WHERE rl.customer_trx_id = p_comm_id
AND rl.org_id = p_org_id;
SELECT site.cust_account_id from hz_cust_acct_sites_all site
where site.cust_acct_site_id in
( select uses.cust_acct_site_id
from hz_cust_site_uses_all uses
where site_use_id = p_bill_to_site_use_id
and site_use_code = 'BILL_TO')
and nvl(site.org_id,p_org_id) = p_org_id;
SELECT Object1_id1,
Number_of_items,
UOM_code,
object1_id2
FROM OKC_K_ITEMS
WHERE CLE_ID = p_id;
SELECT primary_uom_code
From mtl_system_items_b
WHERE inventory_item_id = p_id
AND organization_id = p_org_id;
SELECT contact.object1_id1
FROM okc_contacts contact,
okc_k_party_roles_b party
WHERE contact.cpl_id = party.id --p_cpl_id
AND contact.cro_code = p_code
AND party.rle_code in ('VENDOR','MERCHANT')
AND party.dnz_chr_id = p_dnz_chr_id
AND party.cle_id is null;
SELECT to_char(ole.date_start,'YYYY/MM/DD')
FROM oks_level_elements ole
WHERE ole.cle_id = p_cle_id
AND trunc(p_start_date) >= trunc(ole.date_start)
ORDER BY ole.date_start desc;
G_RAIL_REC.LAST_UPDATED_BY := FND_GLOBAL.user_id;
G_RAIL_REC.LAST_UPDATE_DATE := sysdate;
select description from fnd_lookups
where lookup_type = 'OKS_BILL_ACTIONS'
and lookup_code = p_bill_action;
SELECT str.invoice_offset_days --str.action_offset_days
FROM oks_stream_levels_b str,
oks_level_elements lvl
WHERE lvl.cle_id = p_cle_id
AND trunc(p_date_billed_from) between trunc(lvl.date_start)
and trunc(lvl.date_end)
AND lvl.rul_id = str.id;
SELECT Isa_agreement_id
FROM OKC_GOVERNANCES
WHERE dnz_Chr_id = p_id
AND cle_id Is Null;
SELECT Contact.object1_id1 , Contact.cro_code
FROM Okc_contacts Contact
,Okc_k_party_roles_B Party
WHERE Contact.cpl_id = Party.id
AND Contact.cro_code in ('CUST_BILLING','CUST_SHIPPING')
AND p_date_billed_from between nvl(contact.start_date,p_date_billed_From) and
nvl(contact.end_date,p_date_billed_from)
AND party.dnz_chr_id = p_hdr_id
AND party.cle_id = p_cle_id
AND party.jtot_object1_code = 'OKX_PARTY';
procedure insert_ra_interface(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
BEGIN
x_return_status := 'S';
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface => AMOUNT'||G_RAIL_REC.AMOUNT);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface => BATCH_SOURCE_NAME'||G_RAIL_REC.BATCH_SOURCE_NAME);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface => CURRENCY_CODE'||G_RAIL_REC.CURRENCY_CODE);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface => SET_OF_BOOKS_ID '||G_RAIL_REC.SET_OF_BOOKS_ID);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface => ORG_ID '||G_RAIL_REC.ORG_ID);
INSERT INTO RA_INTERFACE_LINES_ALL (
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_DATE
,GL_DATE
,PRINTING_OPTION
,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_SHIP_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
,RULE_END_DATE
,SALES_ORDER
,SALES_ORDER_LINE
,CONTRACT_LINE_ID
,SALES_ORDER_DATE
,SALES_ORDER_SOURCE
,SET_OF_BOOKS_ID
,TAX_EXEMPT_FLAG
,TAX_EXEMPT_NUMBER
,TAX_EXEMPT_REASON_CODE
,TAX_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
,TRANSLATED_DESCRIPTION
,invoiced_line_acctg_level
,Source_data_key1
,Source_data_key2
,Source_data_key3
,Source_data_key4
,Source_data_key5
,reference_line_attribute1
,reference_line_attribute2
,reference_line_attribute3
,reference_line_attribute4
,reference_line_attribute5
,reference_line_attribute6
,reference_line_attribute7
,reference_line_attribute8
,reference_line_attribute9
,reference_line_attribute10
,reference_line_context
,deferral_exclusion_flag
,parent_line_id
,payment_trxn_extension_id
,warehouse_id
)
VALues (
G_RAIL_REC.ACCOUNTING_RULE_ID
,G_RAIL_REC.ACCOUNTING_RULE_DURATION
,G_RAIL_REC.AGREEMENT_ID
,G_RAIL_REC.AMOUNT
,G_RAIL_REC.BATCH_SOURCE_NAME
,G_RAIL_REC.COMMENTS
,G_RAIL_REC.CONVERSION_DATE
,G_RAIL_REC.CONVERSION_RATE
,nvl(G_RAIL_REC.CONVERSION_TYPE, 'User')
,G_RAIL_REC.CREATED_BY
,G_RAIL_REC.CREATION_DATE
,G_RAIL_REC.CREDIT_METHOD_FOR_ACCT_RULE
,G_RAIL_REC.CREDIT_METHOD_FOR_INSTALLMENTS
,G_RAIL_REC.CURRENCY_CODE
,G_RAIL_REC.CUST_TRX_TYPE_ID
,G_RAIL_REC.DESCRIPTION
,G_RAIL_REC.LAST_UPDATED_BY
,G_RAIL_REC.LAST_UPDATE_DATE
,G_RAIL_REC.LINE_TYPE
,G_RAIL_REC.TRX_DATE
,G_RAIL_REC.GL_DATE
,G_RAIL_REC.PRINTING_OPTION
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE1
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE2
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE3
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE4
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE5
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE6
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE7
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE8
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE9
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE10
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE11
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE12
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE13
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE14
,G_RAIL_REC.INTERFACE_LINE_ATTRIBUTE15
,G_RAIL_REC.INTERFACE_LINE_ID
,G_RAIL_REC.INTERFACE_LINE_CONTEXT
,G_RAIL_REC.INVENTORY_ITEM_ID
,G_RAIL_REC.INVOICING_RULE_ID
,G_RAIL_REC.ORIG_SYSTEM_BILL_CUSTOMER_ID
,G_RAIL_REC.ORIG_SYSTEM_BILL_ADDRESS_ID
,G_RAIL_REC.ORIG_SYSTEM_SHIP_CUSTOMER_ID
,G_RAIL_REC.ORIG_SYSTEM_SHIP_ADDRESS_ID
,G_RAIL_REC.ORIG_SYSTEM_BILL_CONTACT_ID
,G_RAIL_REC.ORIG_SYSTEM_SHIP_CONTACT_ID
,G_RAIL_REC.ORIG_SYSTEM_SOLD_CUSTOMER_ID
,G_RAIL_REC.PRIMARY_SALESREP_NUMBER
,G_RAIL_REC.PRIMARY_SALESREP_ID
,G_RAIL_REC.PURCHASE_ORDER
,G_RAIL_REC.PURCHASE_ORDER_REVISION
,G_RAIL_REC.PURCHASE_ORDER_DATE
,G_RAIL_REC.CUSTOMER_BANK_ACCOUNT_ID
,G_RAIL_REC.RECEIPT_METHOD_ID
,G_RAIL_REC.RECEIPT_METHOD_NAME
--,G_RAIL_REC.QUANTITY /** for bug# 1882229 ***/
,G_RAIL_REC.QUANTITY_ORDERED
,G_RAIL_REC.QUANTITY_ORDERED
,G_RAIL_REC.REASON_CODE
,G_RAIL_REC.REASON_CODE_MEANING
,G_RAIL_REC.REFERENCE_LINE_ID
,G_RAIL_REC.RULE_START_DATE
,G_RAIL_REC.RULE_END_DATE
,G_RAIL_REC.SALES_ORDER
,G_RAIL_REC.SALES_ORDER_LINE
,G_RAIL_REC.CONTRACT_LINE_ID
,G_RAIL_REC.SALES_ORDER_DATE
,G_RAIL_REC.SALES_ORDER_SOURCE
,G_RAIL_REC.SET_OF_BOOKS_ID
,G_RAIL_REC.TAX_EXEMPT_FLAG
,G_RAIL_REC.TAX_EXEMPT_NUMBER
,G_RAIL_REC.TAX_EXEMPT_REASON_CODE
,G_RAIL_REC.TAX_CODE
,G_RAIL_REC.TERM_ID /*Check it out */
,G_RAIL_REC.UNIT_SELLING_PRICE
,G_RAIL_REC.UNIT_STANDARD_PRICE
,G_RAIL_REC.UOM_CODE
,G_RAIL_REC.HEADER_Attribute_CATEGORY
,G_RAIL_REC.HEADER_Attribute1
,G_RAIL_REC.HEADER_Attribute2
,G_RAIL_REC.HEADER_Attribute3
,G_RAIL_REC.HEADER_Attribute4
,G_RAIL_REC.HEADER_Attribute5
,G_RAIL_REC.HEADER_Attribute6
,G_RAIL_REC.HEADER_Attribute7
,G_RAIL_REC.HEADER_Attribute8
,G_RAIL_REC.HEADER_Attribute9
,G_RAIL_REC.HEADER_Attribute10
,G_RAIL_REC.HEADER_Attribute11
,G_RAIL_REC.HEADER_Attribute12
,G_RAIL_REC.HEADER_Attribute13
,G_RAIL_REC.HEADER_Attribute14
,G_RAIL_REC.HEADER_Attribute15
,G_RAIL_REC.Attribute_CATEGORY
,G_RAIL_REC.Attribute1
,G_RAIL_REC.Attribute2
,G_RAIL_REC.Attribute3
,G_RAIL_REC.Attribute4
,G_RAIL_REC.Attribute5
,G_RAIL_REC.Attribute6
,G_RAIL_REC.Attribute7
,G_RAIL_REC.Attribute8
,G_RAIL_REC.Attribute9
,G_RAIL_REC.Attribute10
,G_RAIL_REC.Attribute11
,G_RAIL_REC.Attribute12
,G_RAIL_REC.Attribute13
,G_RAIL_REC.Attribute14
,G_RAIL_REC.Attribute15
,G_RAIL_REC.Org_Id
,G_RAIL_REC.TRANSLATED_DESCRIPTION
,G_RAIL_REC.invoiced_line_acctg_level
,G_RAIL_REC.Source_data_key1
,G_RAIL_REC.Source_data_key2
,G_RAIL_REC.Source_data_key3
,G_RAIL_REC.Source_data_key4
,G_RAIL_REC.Source_data_key5
,G_RAIL_REC.reference_line_attribute1
,G_RAIL_REC.reference_line_attribute2
,G_RAIL_REC.reference_line_attribute3
,G_RAIL_REC.reference_line_attribute4
,G_RAIL_REC.reference_line_attribute5
,G_RAIL_REC.reference_line_attribute6
,G_RAIL_REC.reference_line_attribute7
,G_RAIL_REC.reference_line_attribute8
,G_RAIL_REC.reference_line_attribute9
,G_RAIL_REC.reference_line_attribute10
,G_RAIL_REC.reference_line_context
,G_RAIL_REC.deferral_exclusion_flag
,G_RAIL_REC.parent_line_id
,G_RAIL_REC.payment_trxn_extension_id
,G_RAIL_REC.warehouse_id
);
'OKS_ARFEEDER_PUB.insert_ra_interface => Exception in insert into RA_INTERFACE_LINES '||' SQLCOE = '||sqlcode ||' Sqlerrm = '||sqlerrm);
End insert_ra_interface;
procedure insert_ra_rev_dist(
x_return_status OUT NOCOPY VARCHAR2,
p_cle_id IN NUMBER
)
IS
CURSOR rev_dist_cur(p_cle_id NUMBER) IS
SELECT ACCOUNT_CLASS,
CODE_COMBINATION_ID,
PERCENT
FROM oks_rev_distributions
WHERE cle_id = p_cle_id;
INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL
(ACCOUNT_CLASS,
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
(
l_rev_dist.account_class,
l_rev_dist.percent,
l_rev_dist.code_combination_id,
G_RAIL_REC.interface_line_context,
G_RAIL_REC.interface_line_attribute1,
G_RAIL_REC.interface_line_attribute2,
G_RAIL_REC.interface_line_attribute3,
G_RAIL_REC.interface_line_attribute4,
G_RAIL_REC.interface_line_attribute5,
G_RAIL_REC.interface_line_attribute6,
G_RAIL_REC.interface_line_attribute7,
G_RAIL_REC.interface_line_attribute8,
G_RAIL_REC.interface_line_attribute9,
G_RAIL_REC.interface_line_attribute10,
G_RAIL_REC.interface_line_attribute11,
G_RAIL_REC.interface_line_attribute12,
G_RAIL_REC.interface_line_attribute13,
G_RAIL_REC.interface_line_attribute14,
G_RAIL_REC.interface_line_attribute15,
G_RAIL_REC.org_id
) ;
'OKS_ARFEEDER_PUB.insert_ra_rev_dist => Exception in insert into RA_INTERFACE_DISTRIBUTIONS'||' SQLCODE ='||SQLCODE||' SQLERRM = '||SQLERRM);
procedure insert_ra_interface_sc(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_called_from IN NUMBER
)
IS
BEGIN
x_return_status := 'S';
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface_sc => AMOUNT'||G_RAISC_REC.SALES_CREDIT_AMOUNT_SPLIT);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.insert_ra_interface_sc => PERCENT'||G_RAISC_REC.SALES_CREDIT_PERCENT_SPLIT);
G_RAISC_REC.LAST_UPDATED_BY := G_RAIL_REC.LAST_UPDATED_BY;
G_RAISC_REC.LAST_UPDATE_DATE := G_RAIL_REC.LAST_UPDATE_DATE;
AR_InterfaceSalesCredits_GRP.insert_salescredit(
p_salescredit_rec => G_RAISC_REC,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in insert RA_INTERFACE_SALESCREDITS ' || sqlerrm);
'OKS_ARFEEDER_PUB.insert_ra_interface_sc => Exception in insert into RA_INTERFACE_SALESCREDITS'||' SQLCODE = '||SQLCODE ||' SQLERRM = '||SQLERRM);
End insert_ra_interface_sc;
Select Ctc_id
,sales_credit_type_id1
,percent, sales_group_id
From OKS_K_SALES_CREDITS
Where cle_id = p_id;
Select Ctc_id
,sales_credit_type_id1
,percent
,sales_group_id
From OKS_K_SALES_CREDITS
Where chr_id = p_hdr_id
And cle_id IS NULL;
Insert_ra_interface_sc
(
l_return_status,
l_msg_cnt,
l_msg_data,
p_called_from
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Sales_credit => Insert into RA_Interface_SalesCredit Failed For header id '||p_hdr_id);
Insert_ra_interface_sc
(
X_RETURN_STATUS => L_return_status,
X_MSG_COUNT => l_msg_cnt,
X_MSG_DATA => l_msg_data,
P_CALLED_FROM => p_called_from);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Sales_credit => Insert into RA_Interface_SalesCredit Failed For'||p_id);
SELECT quantity,
unit_price,
amount
FROM OKS_PRICE_BREAKS
WHERE bsl_id = p_id
ORDER BY quantity_from;
SELECT abs(txl.extended_amount) extended_amount,
txl.quantity_ordered,
txl.quantity_invoiced,
txl.unit_selling_price,
txl.gross_unit_selling_price,
txl.gross_extended_amount,
txl.amount_includes_tax_flag,
txl.customer_trx_line_id ,
txh.trx_date
FROM ra_customer_trx_all txh,
ra_customer_trx_lines_all txl
WHERE txl.interface_line_attribute1 = p_contract_number
AND nvl(txl.interface_line_attribute2,'-') = nvl(p_contract_modifier,'-')
AND txl.interface_line_attribute3 = p_bill_instance_number
AND txl.interface_line_context = 'OKS CONTRACTS'
AND txl.customer_trx_id = txh.customer_trx_id
AND txl.extended_amount > 0
AND txl.sales_order = p_contract_number|| decode(p_contract_modifier,null,'','-'||p_contract_modifier)
ORDER BY abs(txl.extended_amount);
SELECT extended_amount tax_amount
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE line_type = 'TAX'
AND link_to_cust_trx_line_id = p_customer_trx_line_id;
Insert_ra_interface
(
x_return_status,
x_msg_cnt,
x_msg_data
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert into RA_Interface_Lines Failed while inserting price breaks ' );
INSERT_RA_REV_DIST( x_return_status,
p_bcl_cle_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert INTO RA_REVENUE_DISTRIBUTIONS failed for Price Break');
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert INTO RA_SALES_CREDIT FAILED for Price Breaks');
UPDATE oks_bill_txn_lines
SET cycle_refrence = l_int_att10||' for PB'
WHERE bill_instance_number = TO_NUMBER(G_RAIL_REC.interface_line_attribute3);
Insert_ra_interface
(
x_return_status,
x_msg_cnt,
x_msg_data
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert into RA_Interface_Lines Failed while inserting price breaks ' );
INSERT_RA_REV_DIST( x_return_status,
p_bcl_cle_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert INTO RA_REVENUE_DISTRIBUTIONS failed for Price Break');
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_price_breaks => Insert INTO RA_SALES_CREDIT FAILED for Price Breaks');
SELECT name
FROM AR_RECEIPT_METHODS
WHERE RECEIPT_METHOD_ID = pay_id
AND SYSDATE >= NVL(START_DATE, SYSDATE)
AND SYSDATE <= NVL(END_DATE, SYSDATE)
/* Commented and Modification done by sjanakir for Bug #6855301
AND PAYMENT_TYPE_CODE = 'CREDIT_CARD'; */
'OKS_ARFEEDER_PUB.Get_Pay_Method_Info => Exception in insert into GET_PAY_METHOD'||' SQLCODE = '||SQLCODE||' SQLERRM = '||SQLERRM);
Select a.cust_account_id,
a.cust_acct_site_id,
a.location_id,
c.party_id,
a.id1
From Okx_cust_site_uses_v a, okx_customer_accounts_v c
Where a.id1 = p_id
And c.id1 = a.cust_account_id
AND a.site_use_code = Code;
SELECT ca.cust_account_id, ca.cust_acct_site_id ,
l.location_id ,hca.party_id ,cs.site_use_iD
FROM hz_locations l,
hz_party_sites ps,
hz_cust_accounts hca,
hz_cust_acct_sites_all ca,
hz_cust_site_uses_all cs
WHERE cs.site_use_id = p_id
AND cs.site_use_code = p_code
AND ca.cust_acct_site_id = cs.cust_acct_site_id
AND ps.location_id = l.location_id
AND l.content_source_type = 'USER_ENTERED'
AND ps.party_site_id = ca.party_site_id
AND hca.cust_account_id = ca.cust_account_id;
Select a.id ,
a.btn_id ,
a.date_billed_from,
a.date_billed_to,
a.cle_id,
a.amount,
a.bill_action,
b.dnz_chr_id,
a.date_next_invoice,
b.start_date,
b.lse_id,
a.currency_code,
b.date_terminated ,
c.name lse_name,
b.cust_acct_id,
KLN.trxn_extension_id,
b.line_number top_line_number
From OKC_LINE_STYLES_V c,
OKS_BILL_CONT_LINES a,
OKC_K_LINES_B b,
OKS_K_LINES_B KLN
Where a.btn_id Is Null
And a.amount Is Not Null
And b.id = a.cle_id
And b.id = p_cle_id
AND b.id = KLN.cle_id
And a.bill_action not in ('TR','STR')
And c.id = b.lse_id
And p_cle_id is not null;
Select a.id ,
a.btn_id,
a.date_billed_from,
a.date_billed_to,
a.cle_id,
a.amount,
a.bill_action,
b.dnz_chr_id,
a.date_next_invoice,
b.start_date,
b.lse_id,
a.currency_code,
b.date_terminated,
c.name lse_name,
b.cust_acct_id,
KLN.trxn_extension_id,
b.line_number top_line_number
From OKC_LINE_STYLES_V c,
OKS_BCL_PR a,
OKC_K_LINES_B b,
OKS_K_LINES_B KLN
Where a.btn_id is null
And a.amount Is Not Null
And b.id = a.cle_id
And b.id = p_cle_id
AND b.id = KLN.cle_id
And c.id = b.lse_id
And a.bill_action not in ('AV','TR','STR')
And p_cle_id is not null;
Select /*+ PARALLEL(a) */
a.id ,
a.btn_id ,
a.date_billed_from,
a.date_billed_to,
a.cle_id,
a.amount,
a.bill_action,
b.dnz_chr_id,
a.date_next_invoice,
b.start_date,
b.lse_id,
a.currency_code,
b.date_terminated,
c.name lse_name,
b.cust_acct_id,
KLN.trxn_extension_id,
b.line_number top_line_number
From
OKC_LINE_STYLES_V c,
OKS_BILL_CONT_LINES a,
OKC_K_LINES_B b,
OKS_K_LINES_B KLN
Where a.btn_id is null
And a.amount Is Not Null
And b.id = a.cle_id
AND b.id = KLN.cle_id
And c.id = b.lse_id
And a.bill_action in ('AV','TR','STR','SRI','RI')
And p_cle_id is null;
SELECT
nvl(rhdr.hold_billing,'N') Hold_Billing_flag,
nvl(rhdr.ar_interface_yn,'N') ar_interface_yn, /* Added By sjanakir for Bug #6821826 */
hdr.inv_rule_id , -- IRE
rhdr.acct_rule_id , -- ARL
rhdr.inv_trx_type , --SBG
nvl(rhdr.summary_trx_yn,'N') summary_trx_yn, --SBG
hdr.payment_term_id , --PTR
rhdr.tax_exemption_id , --TAX
rhdr.tax_status , --TAX
hdr.conversion_type , --CVN
hdr.conversion_rate , --CVN
hdr.conversion_rate_date , --CVN
hdr.bill_to_site_use_id , --BTO
hdr.ship_to_site_use_id , --BTO
rhdr.commitment_id,
hdr.contract_number,
hdr.contract_number_modifier,
hdr.attribute_category,
hdr.attribute1,
hdr.attribute2,
hdr.attribute3,
hdr.attribute4,
hdr.attribute5,
hdr.attribute6,
hdr.attribute7,
hdr.attribute8,
hdr.attribute9,
hdr.attribute10,
hdr.attribute11,
hdr.attribute12,
hdr.attribute13,
hdr.attribute14,
hdr.attribute15,
hdr.currency_code,
hdr.authoring_org_id,
hdr.org_id,
nvl(line.cust_po_number,hdr.cust_po_number) cust_po_number,
nvl(line.payment_type,rhdr.payment_type) payment_type,
rhdr.trxn_extension_id,
--Start fixes of eBTax uptake bug#4756579
rhdr.exempt_certificate_number,
rhdr.exempt_reason_code
--End fixes of eBtax uptake bug#4756579
FROM
OKS_K_HEADERS_B rhdr,
OKC_K_HEADERS_B hdr,
OKS_K_LINES_B line
WHERE line.cle_id = p_cle_id
AND hdr.id = line.dnz_chr_id
AND hdr.id = rhdr.chr_id;
SELECT line.bill_to_site_use_id ,
line.ship_to_site_use_id,
rline.commitment_id,
rline.tax_code,
rline.tax_status,
rline.tax_exemption_id,
line.inv_rule_id,
line.cust_acct_id,
rline.acct_rule_id,
rline.invoice_text,
rline.inv_print_flag,
rline.usage_type,
--Start fixes of eBTax uptake bug#4756579
rline.exempt_certificate_number,
rline.exempt_reason_code,
rline.tax_classification_code
--End fixes of eBtax uptake bug#4756579
FROM OKS_K_LINES_V rline,
OKC_K_LINES_B line
WHERE line.id = p_cle_id
AND rline.cle_id = line.id;
Select min(date_to_interface)
From oks_bill_sub_lines
Where bcl_id = p_bcl_id;
Select a.id,
a.cle_id,
a.date_billed_from,
a.date_billed_to,
a.average,
a.amount ,
b.date_terminated,
c.cle_id top_line_id ,
b.lse_id ,
rline.invoice_text,
rline.inv_print_flag,
b.line_number sub_line_number
From OKS_BILL_SUB_LINES a,
OKS_BILL_CONT_LINES c,
OKS_K_LINES_V rline,
OKC_K_LINES_B b
Where a.bcl_id = id_in
AND c.id = a.bcl_id
AND a.cle_id = b.id
AND rline.cle_id = b.id;
Select
id
,start_date
,end_date
,item_description
,block23text
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category
From OKC_K_LINES_V
Where id = id_in;
SELECT site.cust_account_id from hz_cust_acct_sites_all site
where site.cust_acct_site_id in
( select uses.cust_acct_site_id
from hz_cust_site_uses_all uses
where site_use_id = p_bill_to_site_use_id
and site_use_code = 'BILL_TO')
and nvl(site.org_id,p_org_id) = p_org_id
and site.cust_account_id = p_cust_account_id;
SELECT v2.exempt_certificate_number,
v2.exempt_reason_code
FROM zx_exemptions v2
WHERE (trunc(l_trx_date) BETWEEN trunc(v2.EFFECTIVE_FROM)
AND nvl( trunc(v2.EFFECTIVE_TO), trunc(l_trx_date)))
AND v2.tax_exemption_id = id_in;
SELECT tax_classification_code
FROM zx_id_tcc_mapping
WHERE tax_rate_code_id = p_id
AND org_id = p_org_id;
SELECT v2.exempt_certificate_number,
v2.exempt_reason_code
FROM zx_exemptions v2
WHERE (trunc(p_trx_date) BETWEEN trunc(v2.EFFECTIVE_FROM)
AND nvl( trunc(v2.EFFECTIVE_TO), trunc(p_trx_date)))
AND v2.exempt_certificate_number = p_exempt_number;
Select cl.lse_id,lsl.name
From OKC_LINE_STYLES_V lsl, OKC_K_LINES_B cl
Where lsl.id = cl.lse_id
And cl.id = id_in;
SELECT type,frequency
FROM ra_rules
WHERE rule_id = p_id;
/* Above select avoids OKX view usage
Select type
From OKX_RULES_V
Where id1 = p_id;
Select Contract_number
,Contract_number_modifier
,authoring_org_id
,org_id
,currency_code
,cust_po_number
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category
From OKC_K_HEADERS_B
Where id = p_id;
SELECT btl.bsl_id ,bcl.btn_id from oks_bill_txn_lines btl,
oks_bill_cont_lines bcl
WHERE bcl.cle_id = p_cle_id
AND trunc(bcl.date_billed_from) <= trunc(p_date_from)
AND trunc(bcl.date_billed_to) = trunc(p_date_to)
AND bcl.bill_action = 'RI'
AND bcl.btn_id = btl.btn_id ;
SELECT last_oe_order_line_id from csi_item_instances
where instance_id in (select to_number(itm.object1_id1) from okc_k_items itm
where itm.cle_id = p_id
and jtot_object1_code = 'OKX_CUSTPROD');
Select Ctc_id
,sales_credit_type_id1
,percent
From OKS_K_SALES_CREDITS
Where cle_id = p_id;
SELECT count(1)
FROM oks_price_breaks
WHERE bsl_id = p_id;
SELECT Number_of_items,
UOM_code
FROM OKC_K_ITEMS
WHERE CLE_ID = p_id;
SELECT interface_price_break
FROM OKS_K_DEFAULTS
WHERE cdt_type = 'MDT';
SELECT average
FROM oks_bill_sub_lines
WHERE id = p_id;
SELECT count(*)
FROM ra_customer_trx_all d, --Okx_customer_trx_v d
ra_customer_trx_lines_all c, --Okx_cust_trx_lines_v c
ra_cust_trx_types_all types
WHERE c.interface_line_attribute1 = p_contract_number
AND nvl(c.interface_line_attribute2,'-') = nvl(p_contract_modifier ,'-')
AND c.Interface_line_attribute3 = to_char(p_bill_instance_number)
AND c.Interface_line_context = 'OKS CONTRACTS'
AND c.customer_trx_id = d.customer_trx_id
AND c.sales_order = p_contract_number|| decode(p_contract_modifier,null,'','-'||p_contract_modifier)
AND d.cust_trx_type_id = types.cust_trx_type_id
AND types.type = 'INV' ;
Select bsd.result result,
bsd.unit_of_measure uom_code
From oks_bill_sub_line_dtls bsd
Where bsd.bsl_id = p_id;
Select usage_type
From oks_k_lines_b kln
Where kln.cle_id = p_id;
SELECT c.party_id,
a.party_site_id
FROM hz_cust_acct_sites a,
hz_cust_site_uses b,
hz_party_sites c
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND c.party_site_id = a.party_site_id
AND b.site_use_id = p_site_use_id;
SELECT BATCH_SOURCE_ID
FROM ra_batch_sources_all
WHERE org_id = p_org_id
AND NAME = 'OKS_CONTRACTS';
l_fail_stat NUMBER := 0; /* set to 1 if any inser or update failed */
l_select_counter NUMBER := 0;
SELECT receipt_method_id
INTO l_pay_method_id
FROM ra_cust_receipt_methods
WHERE cust_receipt_method_id = l_cust_pay_method_id;
SELECT ic.ccnumber,
NVL(ic.expirydate,SYSDATE +1)
INTO l_cc_no,
l_exp_date
FROM iby_creditcard ic,
iby_pmt_instr_uses_all ipa,
iby_fndcpt_tx_extensions ifte
WHERE ifte.trxn_extension_id = p_trxn_extension_id
AND ifte.instr_assignment_id = ipa.instrument_payment_use_id
AND ipa.instrument_id = ic.instrid;
l_select_counter := l_select_counter + 1;
OKS_BILLTRAN_PUB.insert_Bill_Tran_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_ret_stat,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_btnv_tbl => l_btnv_tbl_in,
x_btnv_tbl => l_btnv_tbl_out
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After calling OKS_BILLTRAN_PUB.insert_Bill_Tran_Pub l_ret_status'||l_ret_stat);
l_btn_pr_tbl_in(1).LAST_UPDATED_BY := FND_GLOBAL.user_id;
l_btn_pr_tbl_in(1).LAST_UPDATE_DATE := sysdate;
l_btn_pr_tbl_in(1).LAST_UPDATE_LOGIN := FND_GLOBAL.user_id;
OKS_BILLTRAN_PRV_PUB.insert_btn_pr
(
p_api_version => 1.0 ,
p_init_msg_list => 'T',
x_return_status => l_ret_stat ,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data ,
p_btn_pr_tbl => l_btn_pr_tbl_in ,
x_btn_pr_tbl => l_btn_pr_tbl_out
);
UPDATE oks_bill_cont_lines
SET btn_id = l_btnv_tbl_out(1).id
WHERE id = bcl_rec.id ;
UPDATE oks_bcl_pr
SET BTN_ID = l_btn_pr_tbl_in(1).ID
WHERE ID = bcl_rec.id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into Bill_Transactions Failed For Line : '||Bcl_rec.cle_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Updated all G_RAIL Field');
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Before call to insert bill_txn_line');
OKS_BILLTRAN_LINE_PUB.insert_Bill_Tran_Line_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_ret_stat,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_btlv_tbl => l_btlv_tbl_in,
x_btlv_tbl => l_btlv_tbl_out
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After calling OKS_BILLTRAN_LINE_PUB.insert_Bill_Tran_Line_Pub l_return_status'||l_ret_stat);
l_btl_pr_tbl_in(1).LAST_UPDATED_BY := FND_GLOBAL.user_id;
l_btl_pr_tbl_in(1).LAST_UPDATE_LOGIN:=FND_GLOBAL.user_id;
l_btl_pr_tbl_in(1).LAST_UPDATE_DATE := sysdate;
OKS_BILLTRAN_LINE_PRV_PUB.insert_btl_pr(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_ret_stat,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_btl_pr_tbl => l_btl_pr_tbl_in,
x_btl_pr_tbl => l_btl_pr_tbl_out);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER Insert into Bill_Tran_lines Failed For'||Bcl_rec.cle_id);
Insert_ra_interface
(
l_ret_stat,
l_msg_cnt,
l_msg_data
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into RA_Interface_Lines Failed For'||Bcl_rec.cle_id);
UPDATE oks_bill_txn_lines
SET cycle_refrence = G_RAIL_REC.interface_line_attribute10
WHERE bill_instance_number = TO_NUMBER(G_RAIL_REC.interface_line_attribute3);
insert_ra_rev_dist( l_ret_stat,bcl_rec.cle_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_REVENUE_DISTRIBUTIONS'||lines_rec.id);
OKS_BILLTRAN_LINE_PUB.insert_Bill_Tran_Line_Pub
(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_ret_stat,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_btlv_tbl => l_btlv_tbl_in,
x_btlv_tbl => l_btlv_tbl_out
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => After call to insert bill_txn_lines l_return_status '||l_ret_stat);
l_btl_pr_tbl_in(1).LAST_UPDATED_BY := FND_GLOBAL.user_id;
l_btl_pr_tbl_in(1).LAST_UPDATE_LOGIN:=FND_GLOBAL.user_id;
l_btl_pr_tbl_in(1).LAST_UPDATE_DATE := sysdate;
OKS_BILLTRAN_LINE_PRV_PUB.insert_btl_pr(
p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_ret_stat,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_btl_pr_tbl => l_btl_pr_tbl_in,
x_btl_pr_tbl => l_btl_pr_tbl_out);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into Bill_Tran_lines Failed For'||bsl_rec.cle_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Before insert into ra_interface_lines');
Insert_ra_interface
(
l_ret_stat,
l_msg_cnt,
l_msg_data
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into RA_Interface_Lines Failed For'||bsl_rec.cle_id);
INSERT_RA_REV_DIST( l_ret_stat,
bcl_rec.cle_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_REVENUE_DISTRIBUTIO NS'||lines_rec.id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_SALES_CREDIT FAILED'||lines_rec.id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into RA_Interface_Lines Failed For'||bsl_rec.cle_id);
Insert_ra_interface
(
l_ret_stat,
l_msg_cnt,
l_msg_data
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert into RA_Interface_Lines Failed For'||bsl_rec.cle_id);
UPDATE oks_bill_txn_lines
SET cycle_refrence = G_RAIL_REC.interface_line_attribute10
WHERE bill_instance_number = TO_NUMBER(G_RAIL_REC.interface_line_attribute3);
INSERT_RA_REV_DIST( l_ret_stat,bcl_rec.cle_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_REVENUE_DISTRIBUTIONS'||lines_rec.id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert INTO RA_SALES_CREDIT FAILED'||lines_rec.id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert Failed IN ARFEEDER MAIN_CUR_EXCEPTION RAISED '||sqlerrm);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert Failed IN ARFEEDER OTHERS EXCEPTION RAISED'||sqlerrm);
select count(id) into l_subline_count from oks_bill_sub_lines where bcl_id = Bcl_rec.id;
select count(id) into l_subline_count from oks_bill_sub_lines where bcl_id = Bcl_rec.id;
select count(id) into l_subline_count from oks_bsl_pr where bcl_id = Bcl_rec.id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert Failed IN ARFEEDER G_BILLING_EXCEPTION RAISED '||sqlerrm);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Get_REC_FEEDER => Insert Failed IN ARFEEDER WHEN OTHERS EXCEPTION RAISED'||sqlerrm);
select interface_line_attribute4, interface_line_attribute5, interface_line_attribute6,
interface_line_attribute7, interface_line_attribute8, interface_line_attribute9,
interface_line_attribute10
from ra_interface_lines_all
WHERE interface_line_attribute1 = p_contract_number
and interface_line_attribute3 = to_char(p_instance_number);
SELECT pb.quantity_from,
pb.unit_price,
pb.amount,
btl.bsl_id
FROM OKS_PRICE_BREAKS pb,
oks_bill_txn_lines btl
WHERE pb.bsl_id = btl.bsl_id
AND btl.bill_instance_number = p_bill_inst_no
AND pb.amount > 0
ORDER BY pb.quantity_from;
SELECT quantity,
unit_price,
amount, quantity_from
FROM OKS_PRICE_BREAKS
WHERE bsl_id = p_bsl_id
ORDER BY abs(amount);
Insert_ra_interface
(
x_return_status,
x_msg_cnt,
x_msg_data
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert into RA_Interface_Lines Failed while inserting price breaks ' );
INSERT_RA_REV_DIST( x_return_status,
p_bcl_cle_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert INTO RA_REVENUE_DISTRIBUTIONS failed for Price Break');
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert INTO RA_SALES_CREDIT FAILED for Price Breaks');
Insert_ra_interface
(
x_return_status,
x_msg_cnt,
x_msg_data
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert into RA_Interface_Lines Failed while inserting price breaks ' );
INSERT_RA_REV_DIST( x_return_status,
p_bcl_cle_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert INTO RA_REVENUE_DISTRIBUTIONS failed for Price Break');
FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_ARFEEDER_PUB.Set_Reference_PB_Value => Insert INTO RA_SALES_CREDIT FAILED for Price Breaks');