[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
CLAIM_ID
,CLAIM_NUMBER
,CLAIM_TYPE_ID
,CLAIM_CLASS
,CLAIM_DATE
,DUE_DATE
,AMOUNT
,AMOUNT_ADJUSTED
,AMOUNT_REMAINING
,AMOUNT_SETTLED
,ACCTD_AMOUNT
,ACCTD_AMOUNT_REMAINING
,TAX_CODE
,TAX_CALCULATION_FLAG
,CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE_DATE
,EXCHANGE_RATE
,SET_OF_BOOKS_ID
,CUST_ACCOUNT_ID
,CUST_BILLTO_ACCT_SITE_ID
,CUST_SHIPTO_ACCT_SITE_ID
,LOCATION_ID
,PAY_RELATED_ACCOUNT_FLAG
,RELATED_CUST_ACCOUNT_ID
,RELATED_SITE_USE_ID
,RELATIONSHIP_TYPE
,VENDOR_ID
,VENDOR_SITE_ID
,REASON_TYPE
,REASON_CODE_ID
,STATUS_CODE
,CUSTOMER_REF_DATE
,CUSTOMER_REF_NUMBER
,GL_DATE
,PAYMENT_METHOD
,PAYMENT_REFERENCE_ID
,PAYMENT_REFERENCE_NUMBER
,PAYMENT_REFERENCE_DATE
,PAYMENT_STATUS
,SETTLED_DATE
,EFFECTIVE_DATE
,COMMENTS
,ORG_ID
,LEGAL_ENTITY_ID
,SOURCE_OBJECT_CLASS
INTO
x_claim_rec.claim_id
,x_claim_rec.claim_number
,x_claim_rec.claim_type_id
,x_claim_rec.claim_class
,x_claim_rec.claim_date
,x_claim_rec.due_date
,x_claim_rec.amount
,x_claim_rec.amount_adjusted
,x_claim_rec.amount_remaining
,x_claim_rec.amount_settled
,x_claim_rec.acctd_amount
,x_claim_rec.acctd_amount_remaining
,x_claim_rec.tax_code
,x_claim_rec.tax_calculation_flag
,x_claim_rec.currency_code
,x_claim_rec.exchange_rate_type
,x_claim_rec.exchange_rate_date
,x_claim_rec.exchange_rate
,x_claim_rec.set_of_books_id
,x_claim_rec.cust_account_id
,x_claim_rec.cust_billto_acct_site_id
,x_claim_rec.cust_shipto_acct_site_id
,x_claim_rec.location_id
,x_claim_rec.pay_related_account_flag
,x_claim_rec.related_cust_account_id
,x_claim_rec.related_site_use_id
,x_claim_rec.relationship_type
,x_claim_rec.vendor_id
,x_claim_rec.vendor_site_id
,x_claim_rec.reason_type
,x_claim_rec.reason_code_id
,x_claim_rec.status_code
,x_claim_rec.customer_ref_date
,x_claim_rec.customer_ref_number
,x_claim_rec.gl_date
,x_claim_rec.payment_method
,x_claim_rec.payment_reference_id
,x_claim_rec.payment_reference_number
,x_claim_rec.payment_reference_date
,x_claim_rec.payment_status
,x_claim_rec.settled_date
,x_claim_rec.effective_date
,x_claim_rec.comments
,x_claim_rec.org_id
,x_claim_rec.legal_entity_id
,x_claim_rec.source_object_class
FROM ozf_claims_all
WHERE claim_id = p_claim_id ;
SELECT customer_ref_number
FROM ozf_claims
WHERE claim_id = cv_claim_id;
select ap_invoices_interface_s.nextval
from dual;
select ap_invoice_lines_interface_s.nextval
from dual;
SELECT --ap.accts_pay_code_combination_id,
ozf.gl_id_ded_clearing,
ozf.payables_source,
ozf.ap_payment_term_id
FROM ap_system_parameters_all ap,
ozf_sys_parameters_all ozf
WHERE ozf.set_of_books_id = ap.set_of_books_id
AND ozf.org_id = cv_org_id;
select invoice_num
from AP_INVOICES
where INVOICE_NUM = cv_invoice_num
and VENDOR_ID = cv_vendor_id;
SELECT claim_currency_amount
, acctd_amount
, tax_code
, claim_line_id
, item_type
, item_id
, item_description
, source_object_class
, source_object_id
, activity_type
, activity_id
FROM ozf_claim_lines_all
WHERE claim_id = cv_claim_id;
SELECT description
FROM mtl_system_items_vl
WHERE inventory_item_id = cv_item_id;
SELECT SUBSTRB(category_desc,1,240)
FROM eni_prod_den_hrchy_parents_v
WHERE category_id = cv_category_id;
SELECT channel_name
FROM ams_media_channels_vl
WHERE channel_id = cv_media_channel_id;
SELECT trx_number
FROM ra_customer_trx_all
WHERE customer_trx_id = cv_trx_id;
SELECT order_number
FROM oe_order_headers_all
WHERE header_id = cv_header_id;
SELECT request_number
FROM ozf_request_headers_vl
WHERE request_header_id = cv_request_id;
SELECT offer_code
FROM ozf_offers
WHERE qp_list_header_id = cv_list_id;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_LINE_OVER_TYPE'
AND lookup_code = cv_code;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_CLAIM_ITEM_TYPE'
AND lookup_code = cv_code;
SELECT accts_pay_code_combination_id
FROM po_vendor_sites
WHERE vendor_site_id = cv_vendor_site_id;
SELECT accts_pay_code_combination_id
FROM financials_system_params_all
WHERE org_id = cv_org_id;
SELECT payment_method_lookup_code
FROM FINANCIALS_SYSTEM_PARAMS_ALL
WHERE org_id = cv_org_id;
SELECT payment_method_lookup_code
FROM po_vendors
WHERE vendor_id = cv_vendor_id;
SELECT payment_method_lookup_code
FROM po_vendor_sites
WHERE vendor_site_id = cv_vendor_site_id; */
INSERT INTO AP_INVOICE_LINES_INTERFACE (
INVOICE_ID
,INVOICE_LINE_ID
,LINE_NUMBER
,LINE_TYPE_LOOKUP_CODE
,AMOUNT
,ACCOUNTING_DATE
,TAX_CLASSIFICATION_CODE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,DIST_CODE_COMBINATION_ID
,ORG_ID
,DESCRIPTION
,APPLICATION_ID
,PRODUCT_TABLE
,REFERENCE_KEY1
,REFERENCE_KEY2
,REFERENCE_KEY3
,REFERENCE_KEY4
,REFERENCE_KEY5
,SOURCE_APPLICATION_ID
,SOURCE_ENTITY_CODE
,SOURCE_EVENT_CLASS_CODE
,INVENTORY_ITEM_ID --Fix for bug # 8576443
,ITEM_DESCRIPTION -- Fix for Bug#8885844
)
VALUES (
l_invoice_id
,l_invoice_line_id
,l_invoice_line_num
,'ITEM'
,l_claim_line_rec.claim_currency_amount
,l_claim_rec.gl_date
,l_claim_line_rec.tax_code
,l_user_id
,l_sys_date
,l_login_id
,l_user_id
,l_sys_date
,l_vendor_clearing_account
,l_claim_rec.org_id
,l_final_descr
,682
,'OZF_CLAIMS_ALL'
,l_claim_rec.claim_id
,l_claim_rec.customer_ref_date
,l_claim_rec.customer_ref_number
,l_claim_rec.customer_reason
,l_claim_line_rec.claim_line_id
,682
,'OZF_CLAIMS'
, 'TRADE_MGT_PAYABLES'
, l_item_id --Fix for bug # 8576443 --ninarasi fix for bug 14071276
, l_claim_line_rec.item_description -- Fix for Bug#8885844
);
INSERT INTO AP_INVOICES_INTERFACE (
INVOICE_ID
, INVOICE_NUM
, INVOICE_DATE
, VENDOR_ID
, VENDOR_SITE_ID
, INVOICE_AMOUNT
, INVOICE_CURRENCY_CODE
, EXCHANGE_RATE
, EXCHANGE_RATE_TYPE
, EXCHANGE_DATE
, TERMS_ID
, DESCRIPTION
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATION_DATE
, CREATED_BY
, SOURCE
, GROUP_ID
--, WORKFLOW_FLAG
, PAYMENT_METHOD_CODE
, GL_DATE
, ACCTS_PAY_CODE_COMBINATION_ID
, ORG_ID
, LEGAL_ENTITY_ID
, APPLICATION_ID
, PRODUCT_TABLE
, REFERENCE_KEY1
, REFERENCE_KEY2
, REFERENCE_KEY3
, REFERENCE_KEY4
, REFERENCE_KEY5
,CALC_TAX_DURING_IMPORT_FLAG
,ADD_TAX_TO_INV_AMT_FLAG
,invoice_type_lookup_code
)
VALUES (
l_invoice_id
,l_claim_number
,l_claim_rec.settled_date
,l_claim_rec.vendor_id
,l_claim_rec.vendor_site_id
,l_amount_settled
,l_claim_rec.currency_code
,l_claim_rec.exchange_rate
,l_claim_rec.exchange_rate_type
,l_claim_rec.exchange_rate_date
,l_term_id
,l_claim_rec.customer_ref_number
,l_sys_date
,l_user_id
,l_login_id
,l_sys_date
,l_user_id
,l_source
,l_source|| ' '||l_claim_rec.claim_id
--,'Y'
,l_payment_method
,l_claim_rec.gl_date
,l_accts_pay_code_comb_id
,l_claim_rec.org_id
,l_claim_rec.legal_entity_id
,682
,'OZF_CLAIMS_ALL'
,l_claim_rec.claim_id
,l_claim_rec.customer_ref_date
,l_claim_rec.customer_ref_number
,l_claim_rec.customer_reason
,NULL
, 'Y'
, 'Y'
-- Added for Bug 10198552
, l_lookup_type_code
);
UPDATE ozf_claims_all
SET payment_reference_id = l_invoice_id
, payment_reference_number = l_claim_number
, payment_reference_date = l_claim_rec.settled_date
, payment_status = 'INTERFACED'
WHERE claim_id = p_claim_id;
UPDATE ozf_claims_all
SET payment_status = 'INTERFACED'
, payment_reference_number = l_claim_number
, payment_reference_date = l_claim_rec.settled_date
, payment_reference_id = l_invoice_id
WHERE claim_id = p_claim_id;