Posts by Sheeba Chandrasekaran

Update Order Type in Customer Standard in Oracle Apps

DECLARE l_init_msg_list VARCHAR2 (2000) := fnd_api.g_true; l_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type; l_object_version_number NUMBER; x_return_status VARCHAR2 (1); x_msg_count NUMBER; x_msg_data VARCHAR2 (2000); l_order_type_id NUMBER; CURSOR order_type_update_cur IS SELECT xotu.*,hcas.cust_acct_site_id,hcsu.object_version_number FROM XXXX_STG xotu,apps.hz_cust_acct_sites_all hcas,apps.hz_cust_site_uses_all hcsu…

Read More

Query to derive Item Cost

SELECT cict.item_number, cict.description, msi.item_type, cict.PLANNING_MAKE_BUY_CODE, mis.inventory_item_status_code_tl AS item_status, sum(cdcv.item_cost)item_cost FROM apps.cst_item_cost_type_v cict, apps.cst_cost_types cct, apps.cst_detail_cost_view cdcv, apps.mtl_system_items_b msi, apps.mtl_item_status mis WHERE cict.inventory_item_id = cdcv.inventory_item_id AND cict.organization_id = cdcv.organization_id AND cict.cost_type_id…

Read More

Sample Code for AP PO Accrual Write off and Close PO

PROCEDURE main ( errbuff OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_file_name IN VARCHAR2 ) IS BEGIN start_process (p_file_name); — write_output; EXCEPTION WHEN OTHERS THEN NULL; END; PROCEDURE start_process (p_filename…

Read More

Query for AP Invoices Pending for Approval

SELECT DISTINCT aiha.invoice_id, response, aiha.creation_date, ai.invoice_date, (SELECT vendor_name FROM apps.po_vendors WHERE vendor_id = ai.vendor_id) vendorname, (SELECT ai.invoice_num FROM ap_invoices_all ai WHERE ai.invoice_id = aiha.invoice_id) invoice_number, (SELECT ai.invoice_amount FROM ap_invoices_all ai…

Read More

Sample code to create a Qualifier for an Existing Modifier

DECLARE lc_uom VARCHAR2 (5); lc_cust_account_id NUMBER; lc_list_type_code VARCHAR2 (150); lc_list_line_type_code VARCHAR2 (150); lc_prod_attr_value VARCHAR2 (25); lc_segment_mapping_column VARCHAR2 (150); lc_prc_context_name VARCHAR2 (150); lc_prc_mapping_column VARCHAR2 (150); lc_prcing_context_name VARCHAR2 (150); lc_account_number VARCHAR2 (15);…

Read More

Query to find the historical Quantity for an item

SELECT SUM (target_qty) FROM (SELECT   segment1, description, mtl.primary_uom_code, moqv.subinventory_code subinv, moqv.inventory_item_id item_id, SUM (transaction_quantity) target_qty FROM apps.mtl_onhand_qty_cost_v moqv, apps.mtl_system_items_vl mtl WHERE moqv.organization_id = :p_organization_id AND moqv.inventory_item_id = :p_inventory_item_id AND moqv.inventory_item_id…

Read More

Creating a New Modifier using Oracle API

When there is a business requirement to create a New modifier, we can make use of this code to Register as a Concurrent Program and create a new Modifier after…

Read More

Customizing the Hook Package IBY_FD_EXTRACT_EXT_PUB

Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract generated by Oracle Payments.   If there is…

Read More

Applying Claim in AR Receipts

declare      ln_trx_number                    VARCHAR2 (50);       v_api_version                    NUMBER                          := 1.0;       —      V_init_msg_list      VARCHAR2(1) := FND_API.G_FALSE;       v_init_msg_list                  VARCHAR2 (1)         := fnd_api.g_true;       v_commit                         VARCHAR2 (1)        := fnd_api.g_false;       v_validation_level              …

Read More

GL Data Extract in EBS for both Receivables and Payables

exec mo_global.set_policy_context(‘S’,:&p_org_id); SELECT DISTINCT gjb.NAME journal_batch_name, gjh.NAME, gjh.default_effective_date gldate, msi.segment1 item, NULL brand, rct.bill_to_site_use_id bill_to_site_use_id, hca.account_number customer, gcc.segment3 “GL ACCT 3”, gcc.segment1 company, rct.interface_header_attribute2 ordertype, rctt.NAME ar_transaction_type, rctl.sales_order order_number, rct.trx_number…

Read More