Oracle Applications Technical

Extract HZ_Geographies data, Query brings Flag set for each Postal Code.

2015-07-20T01:00:00.005-07:00

SELECT a.geography_id,
a.geography_element2,
a.geography_element3_code,
a.geography_name postal_code,
a.geography_element1_code country,
deli.geography_name delivery_flag,
pri.geography_name priority_flag
FROM apps.hz_geographies a,
(SELECT geography_name,
geography_element4_id,
geography_element3,
geography_element2,
geography_element1
FROM apps.hz_geographies
WHERE 1 = 1 AND geography_type = 'DELIVERY AVAILABLE') deli,
(SELECT geography_name,
geography_element4_id,
geography_element3,
geography_element2,
geography_element1
FROM apps.hz_geographies
WHERE 1 = 1 AND geography_type = 'PRIORITY AVAILABLE') pri
WHERE 1 = 1
AND deli.geography_element3(+) = a.geography_element3
AND deli.geography_element2(+) = a.geography_element2
AND deli.geography_element1(+) = a.geography_element1
AND a.geography_id = deli.geography_element4_id(+)
AND pri.geography_element3(+) = a.geography_element3
AND pri.geography_element2(+) = a.geography_element2
AND pri.geography_element1(+) = a.geography_element1
AND a.geography_id = pri.geography_element4_id(+)
-- AND a.GEOGRAPHY_NAME = '2601'--'5254'
AND a.geography_type = 'POSTAL_CODE'

Oracle XMLP Pivot or Matrix report

2015-05-18T18:58:00.002-07:00

This post will explain a Pivot or Matrix report using Oracle XMLP.

Please find the attached XMLP sample reports.

XML File

RTF File

Sample Output

PL/SQL REF Cursor Example

2014-10-13T03:18:00.002-07:00

DECLARE
TYPE array_t IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;

r_array SYS_REFCURSOR;
rec_array array_t;
l_num NUMBER := 5;
l_num1 NUMBER := 3;
BEGIN
OPEN r_array FOR
'SELECT OBJECT_ID FROM ALL_OBJECTS WHERE ROWNUM < :P_NUM AND OBJECT_ID > :P_N'
USING l_num, l_num1;

FETCH r_array
BULK COLLECT INTO rec_array;

FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
DBMS_OUTPUT.put_line (rec_array (i));
END LOOP;

CLOSE r_array;
END;

Query to extract AR Invoice-->Order Details-->Customer and ZX Registration Number --> COGS information (Mtl Trxns)

2014-04-25T01:44:00.002-07:00

SELECT hou.name,
rct.trx_number,
rct.trx_date,
oh.order_number,
ol.line_number Ord_line_num,
rctl.line_number Invoice_line_number,
hp.party_name,
hca.account_number,
zr.registration_number,
hzl.address1,
hzl.address2,
hzl.address3,
hzl.address4,
hzl.city,
hzl.state,
hzl.county,
hzl.province,
hzl.country,
hzl.postal_code,
msi.segment1,
rctl.quantity_ordered,
rctl.uom_code,
rctl.unit_selling_price,
rctl.extended_amount,
(SELECT SUM (mta.base_transaction_value)
FROM mtl_transaction_accounts mta, mtl_material_transactions mtt
WHERE mtt.transaction_id = mta.transaction_id
AND mtt.organization_id = mta.organization_id
AND mta.inventory_item_id = mtt.inventory_item_id
AND mta.base_transaction_value > 0
AND mtt.trx_source_line_id = ol.line_id
AND mtt.transaction_type_id = 10008
AND mtt.transaction_source_type_id = 2
AND ol.inventory_item_id = mtt.inventory_item_id
AND mtt.organization_id = ol.ship_from_org_id)
Unit_cost,
rct.invoice_currency_code
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
oe_order_headers_all oh,
oe_order_lines_all ol,
hz_cust_accounts hca,
hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_locations hzl,
zx_party_tax_profile zptp,
zx_registrations zr,
mtl_system_items msi,
hr_operating_units hou
WHERE rct.customer_trx_id = rctl.customer_trx_id
AND rctl.interface_line_context = 'ORDER ENTRY'
AND rctl.interface_line_attribute6 = ol.line_id
AND oh.header_id = ol.header_id
AND rct.Ship_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND hps.party_id = hp.party_id
AND hps.location_id = hzl.location_id
AND hcas.party_site_id = hps.party_site_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_Site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_id = rctl.ship_to_site_use_id
AND zptp.party_tax_profile_id = zr.party_tax_profile_id
AND zptp.party_id = hp.party_id
AND TRUNC (trx_date) BETWEEN zr.effective_from
AND NVL (zr.effective_to,
TRUNC (SYSDATE + 1))
--AND zr.default_registration_flag = 'Y'
AND hou.organization_id = rctl.org_id
AND msi.inventory_item_id = rctl.inventory_item_id
AND TO_CHAR (msi.organization_id) = rctl.interface_line_attribute10
AND rct.trx_date BETWEEN :p_start_date AND :p_to_date

Oracle APEX Cook Book Second Edition.

2014-04-25T01:39:00.002-07:00

Oracle Apex is a rapid/smart application builder, oracle utility available for free.

In Oracle APEX Cook Book Second Edition Author explained Oracle Apex in detail with required examples, so that beginners can also understand the topics quickly. Apex also provides few packaged application, with this it will be easy to learn and meet complex business requirements.

I never had chance to implement web service and Websheets in APEX after reading this book I found its very useful feature and Author made a best attempt to cover all the features with proper examples.

I recommend this book to all oracle/Apex Developers. Very useful, as a quick reference guide.

http://www.packtpub.com/oracle-apex-cookbook-second-edition/book

Create a record in Per_Competence_Elements using API

2013-12-09T03:29:00.000-08:00

This API may be useful when you customize the Employee Appraisal Process in Oracle EBS.

Note: Get Assigment ID from Per_Assignments table.

declare
l_competence_element_id number; --OUT Parameter
l_comp_object_version_number number; --OUT Parameter
begin

HR_COMPETENCE_ELEMENT_API.create_competence_element(p_validate => false,
p_competence_element_id =>l_competence_element_id,
p_object_version_number =>l_comp_object_version_number,
p_type =>'ASSESSMENT',
p_assessment_id => 5718165, --assessment_id,
--p_assessment_type_id => 13105, --Assessment type id
p_business_group_id =>0,
p_competence_id =>53,
--p_person_id =>8922,
P_comments => 'TEST',
p_parent_competence_element_id =>516,
p_effective_date =>TO_DATE('03-DEC-2013','DD-MON-RRRR'),
p_object_id =>48232 ,--Appraisal ID
p_object_name => 'ASSESSOR_ID',
p_effective_date_from => TO_DATE('03-JAN-2013','DD-MON-RRRR')--,
--p_effective_date_to => TO_DATE('03-JAN-2014','DD-MON-RRRR')
);
commit;
dbms_output.put_line('Competency Added '|| l_competence_element_id);
exception when others then
dbms_output.put_line(substr(sqlerrm,1,250));
dbms_output.put_line(substr(sqlerrm,201,400));
end;

This creates a record in PER_COMPETENCE_ELEMENTS.
For further info leave a comment.

Vendor site and Address - R12 Upgrade Issue

2013-11-26T04:13:00.000-08:00

In R12 each Vendor site will have one location Id and Party Site ID attached to it. After upgarde we identified few vendor sites not having location Id and Party site id attached to it. So as work around we have Created a Location in HZ_LOCATIONS and Party Site in HZ_PARTY_SITES using API's and Updated same to Vendor Site.

DECLARE
   p_location_rec        hz_location_v2pub.location_rec_type;
   x_location_id         NUMBER;
   p_party_site_rec      hz_party_site_v2pub.party_site_rec_type;
   x_party_site_id       NUMBER;
   x_party_site_number   VARCHAR2 (2000);
   x_return_status       VARCHAR2 (2000);
   x_msg_count           NUMBER;
   x_msg_data            VARCHAR2 (2000);
   l_vendor_site_rec     ap_vendor_pub_pkg.r_vendor_site_rec_type;
   v_vendor_id           NUMBER                                   := NULL;
   v_org_id              NUMBER                                   := NULL;
   v_site_id             NUMBER                                   := NULL;
   v_party_id            NUMBER                                   := NULL;
   v_country             VARCHAR2 (250);
   v_address_line1       VARCHAR2 (250);
   v_address_line2       VARCHAR2 (250);
   v_city                VARCHAR2 (250);
   v_zip                 VARCHAR2 (250);
   v_state               VARCHAR2 (250);
   v_resp_id             NUMBER;
   v_created_by          NUMBER;
   x_vendor_site_id      NUMBER;
BEGIN
-- Initilaize variables
   x_location_id := NULL;
   x_party_site_id := NULL;
   x_party_site_number := NULL;
   x_return_status := NULL;
   x_msg_count := NULL;
   x_msg_data := NULL;
   v_vendor_id := NULL;
   v_org_id := NULL;
   v_site_id := NULL;
   v_country := NULL;
   v_address_line1 := NULL;
   v_address_line2 := NULL;
   v_city := NULL;
   v_zip := NULL;
   v_state := NULL;
   v_resp_id := NULL;
-- Get the supplier Details TO CREATE Location AND Party Site
   SELECT sup.vendor_id, site.vendor_site_id, site.org_id, sup.party_id,
          NVL (site.country, 'US'), NVL (site.address_line1, '***'),
          NVL (site.address_line2, '***'), NVL (site.city, '***'), site.zip,
          site.state, site.created_by
     INTO v_vendor_id, v_site_id, v_org_id, v_party_id,
          v_country, v_address_line1,
          v_address_line2, v_city, v_zip,
          v_state, v_created_by
     FROM ap_suppliers sup, ap_supplier_sites_all site
    WHERE sup.vendor_id = site.vendor_id
      AND (site.location_id IS NULL OR site.party_site_id IS NULL)
      AND site.vendor_site_id = 454737;                              --454738;
   IF v_org_id = 94
   THEN
      v_resp_id := 50267;                                               --CAD
   ELSE
      v_resp_id := 50266;                                               --USD
   END IF;
-- Initialize
   fnd_global.apps_initialize (user_id           => v_created_by,
                               resp_id           => v_resp_id,
                               resp_appl_id      => 200
                              );
   mo_global.set_policy_context ('S', v_org_id);
   fnd_global.set_nls_context ('AMERICAN');
-- Below API Call TO CREATE TCA Location
   BEGIN
      p_location_rec.country := v_country;
      p_location_rec.address1 := v_address_line1;
      p_location_rec.address2 := v_address_line2;
      p_location_rec.city := v_city;
      p_location_rec.postal_code := v_zip;
      p_location_rec.state := v_state;
      p_location_rec.created_by_module := 'TCA_V2_API';
      hz_location_v2pub.create_location (p_init_msg_list      => 'T',
                                         p_location_rec       => p_location_rec,
                                         x_location_id        => x_location_id,
                                         x_return_status      => x_return_status,
                                         x_msg_count          => x_msg_count,
                                         x_msg_data           => x_msg_data
                                        );
      DBMS_OUTPUT.put_line (   'x_return_status = '
                            || SUBSTR (x_return_status, 1, 255)
                           );
      DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
      DBMS_OUTPUT.put_line ('Location Id = ' || TO_CHAR (x_location_id));
      DBMS_OUTPUT.put_line (   'Country = '
                            || SUBSTR (p_location_rec.country, 1, 255)
                           );
      DBMS_OUTPUT.put_line (   'Address1 = '
                            || SUBSTR (p_location_rec.address1, 1, 255)
                           );
      DBMS_OUTPUT.put_line ('State = '
                            || SUBSTR (p_location_rec.state, 1, 255)
                           );
      DBMS_OUTPUT.put_line (   'Created By = '
                            || SUBSTR (p_location_rec.created_by_module,
                                       1,
                                       255
                                      )
                           );
      DBMS_OUTPUT.put_line ('x_msg_data = ' || SUBSTR (x_msg_data, 1, 255));
      DBMS_OUTPUT.put_line ('x_location_id = ' || x_location_id);
      IF x_msg_count > 1
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            DBMS_OUTPUT.put_line
                      (   i
                       || '.'
                       || SUBSTR
                               (fnd_msg_pub.get (p_encoded      => fnd_api.g_false),
                                1,
                                255
                               )
                      );
         END LOOP;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
   END;
--*******************************Party Site*****************************************************
-- Below API TO Create Party Site
   IF x_location_id IS NOT NULL AND v_party_id IS NOT NULL
   THEN
      BEGIN
         -- Setting the Context --
         -- Initializing the Mandatory API parameters
         p_party_site_rec.party_id := v_party_id;                  -- 530682;
         p_party_site_rec.location_id := x_location_id;
         p_party_site_rec.identifying_address_flag := 'Y';
         p_party_site_rec.created_by_module := 'TCA_V2_API';
         DBMS_OUTPUT.put_line
                     ('Calling the API hz_party_site_v2pub.create_party_site');
         hz_party_site_v2pub.create_party_site
                                 (p_init_msg_list          => fnd_api.g_true,
                                  p_party_site_rec         => p_party_site_rec,
                                  x_party_site_id          => x_party_site_id,
                                  x_party_site_number      => x_party_site_number,
                                  x_return_status          => x_return_status,
                                  x_msg_count              => x_msg_count,
                                  x_msg_data               => x_msg_data
                                 );
         IF x_return_status = fnd_api.g_ret_sts_success
         THEN
            COMMIT;
            DBMS_OUTPUT.put_line ('Creation of Party Site is Successful ');
            DBMS_OUTPUT.put_line ('Output information ....');
            DBMS_OUTPUT.put_line ('Party Site Id     = ' || x_party_site_id);
            DBMS_OUTPUT.put_line ('Party Site Number = '
                                  || x_party_site_number
                                 );
         ELSE
            DBMS_OUTPUT.put_line (   'Creation of Party Site failed:'
                                  || x_msg_data
                                 );
            ROLLBACK;
            FOR i IN 1 .. x_msg_count
            LOOP
               x_msg_data :=
                         fnd_msg_pub.get (p_msg_index      => i,
                                          p_encoded        => 'F');
               DBMS_OUTPUT.put_line (i || ') ' || x_msg_data);
            END LOOP;
         END IF;
         DBMS_OUTPUT.put_line ('Completion of API');
      END;
   END IF;
--***********************Update Supplier Site********************************************************************
-- Below API call TO UPDATE Supplier site WITH Address, Party Site AND Location ID
   IF x_party_site_id IS NOT NULL AND x_location_id IS NOT NULL
   THEN
      l_vendor_site_rec.vendor_id := v_vendor_id;              -- Supplier Id
      l_vendor_site_rec.org_id := v_org_id;              -- Operating Unit id
      x_vendor_site_id := v_site_id;                 -- Site Id to be updated
      l_vendor_site_rec.vendor_site_id := v_site_id;
      l_vendor_site_rec.location_id := x_location_id;
      l_vendor_site_rec.party_site_id := x_party_site_id;
      ap_vendor_pub_pkg.update_vendor_site
                                     (p_api_version          => 1,
                                      x_return_status        => x_return_status,
                                      x_msg_count            => x_msg_count,
                                      x_msg_data             => x_msg_data,
                                      p_vendor_site_rec      => l_vendor_site_rec,
                                      p_vendor_site_id       => x_vendor_site_id
                                     );
   END IF;
END;
/

Adding new LOV for Form Field using Logical APPS

2012-08-12T08:38:00.000-07:00

We had a requirement to add LOV for Province field in Customer Site Screen in Oracle APPS when country is China.

There are different ways to do it.
1.Enable DFF for context China
2.Logical APPS
3.May be Form Personalization

Populate Custom LOV for Province Field.

Step #1 – Go to Logical Apps Responsibility à AppsRules

Step #2 Create new rule

Enter Rule Name: China Location (Province LOV 1)

Description: China Location (Province LOV 1)

Step #3 Go to Rule Elements block

Enter Element Name: ADDRESS1

Form Name: ARXCUDCI

User Form Name: Customers

Event: When New Item

Block Name: ADDR

Field Name: ADDRESS1

Step #3 Click on Details Button

Security Tab :

Type : Field

Block/Tab: ADDR

Field Name: PROVINCE

Required Check box : Checked

Step #4 Click on Done Button

Step #5 Click on Element Subscribers

Subscriber #1:

Filter Type : Responsibility

Operator : Equal

Value: Receivables Customer Entry

Data Type: Varchar

Field Type: Static

And/Or: And

Group : 1

Subscriber #2:

Filter Type : Data

Filter Name: ADDR.TERRITORY_SHORT_NAME

Operator : Equal

Data Type: Varchar

Field Type: Static

Dependent Value: China

And/Or: Or

Group : 1

Click on Done Button.

Step #5 Create new rules

Enter Rule Name: China Location (Province LOV 2)

Description: China Location (Province LOV 2)

Step #6 Enter Rule Subscribers

Filter Type: Responsibility

Operator: Equal

Value: Receivables Customer Entry

Datatype: Varchar

Field type: Static

Step #7 Rule Elements

Seq: 10

Element Name: PROVINCE

Form Name: ARXCUDCI

User Form Name: Customers

Event Name: When New Item

Block Name: ADDR

Field Name: PROVINCE

Step #8 Click Details button à Security Tab

Type: Field

Block/Tab: ADDR

Field Name: PROVINCE

Required Check box : Checked

Step #9: List of Values Tab:

Block Name: ADDR

Field Name: PROVINCE

Record Group: LA_PROVINCE

SQL Text :

SELECT flex_value NAME, flex_value_meaning VALUE

FROM fnd_flex_values_vl a, fnd_flex_value_sets b

WHERE a.flex_value_set_id = b.flex_value_set_id

AND b.FLEX_VALUE_SET_NAME = 'CN_PROVINCE'

AND a.ENABLED_FLAG = 'Y'

AND NVL(a.START_DATE_ACTIVE, SYSDATE) <= SYSDATE

AND NVL(a.END_DATE_ACTIVE, SYSDATE+1) >= SYSDATE

Step #10: Field Attributes Tab

Seq: 10

Type: Field Instance

Block Name: ADDR

Field Name: PROVINCE

Property: Navigable

Value: True

Function to check the Assembly and Component association using BOM Explosion

2012-06-13T12:36:00.000-07:00

--Below function will return whether given Component is associated with given assembly.
--Compile the function in database
CREATE OR REPLACE FUNCTION xx_bom_expl_fun (
   p_org_id           NUMBER,
   p_assembly_item    VARCHAR2,
   p_component_item   VARCHAR2
)
   RETURN VARCHAR2
IS
   l_err_msg             VARCHAR2 (500);
   l_err_code            NUMBER;
   l_group_id            NUMBER;
   l_session_id          NUMBER         := USERENV ('SID');
   l_seq_id              NUMBER;
   l_assembly_item_id    NUMBER;
   l_component_item_id   NUMBER;
   l_max_level           NUMBER;
   l_cnt                 NUMBER         := 0;
   PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
   SELECT bom_explosion_temp_s.NEXTVAL
     INTO l_group_id
     FROM DUAL;

   l_assembly_item_id := NULL;

   BEGIN
      SELECT inventory_item_id
        INTO l_assembly_item_id
        FROM mtl_system_items
       WHERE segment1 = p_assembly_item AND organization_id = p_org_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_assembly_item_id := NULL;
   END;

   l_component_item_id := NULL;

   BEGIN
      SELECT inventory_item_id
        INTO l_component_item_id
        FROM mtl_system_items
       WHERE segment1 = p_component_item AND organization_id = p_org_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_component_item_id := NULL;
   END;

   l_max_level := NULL;

   BEGIN
      SELECT maximum_bom_level
        INTO l_max_level
        FROM bom_parameters
       WHERE organization_id = p_org_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_max_level := 30;
   END;

   bompexpl.exploder_userexit (0,
                               p_org_id,
                               1,
                               l_group_id,
                               USERENV('SID'),
                               l_max_level,
                               1,
                               1,
                               2,
                               2,
                               2,
                               0,
                               0,
                               1,
                               l_assembly_item_id,
                               '',
                               '',
                               SYSDATE,
                               l_err_msg,
                               l_err_code
                              );
   DBMS_OUTPUT.put_line (l_err_code || ' - '||l_err_msg);

COMMIT;

   SELECT COUNT (*)
     INTO l_cnt
     FROM bom_explosion_temp
    WHERE top_item_id = l_assembly_item_id
      AND component_item_id = l_component_item_id
      AND organization_id = p_org_id;

   IF l_cnt > 0
   THEN
      RETURN 'Yes';
   ELSE
      RETURN 'No';
   END IF;
END;
/

--Example to run the function:
select xx_bom_expl_fun (1 --Organization ID
,'1122045' -- Assembly Number
, '123345657667' --Component Item Number
) from dual

Update the Sales Order Schedule Ship Date - Oracle EBS11i

2012-06-13T12:26:00.002-07:00

-- Temp Table
CREATE TABLE xx_upd_so_line_ssd (
so_number NUMBER,
line VARCHAR2(10),
item VARCHAR2(80),
request_date DATE,
original_promise_date DATE,
schedule_date DATE,
update_ssd DATE);

-- Script to Update SO Line Schedule Ship Date
DECLARE
   l_user_id                      NUMBER;
   l_resp_id                      NUMBER;
   l_appl_id                      NUMBER;
   l_header_rec_in                oe_order_pub.header_rec_type;
   l_line_tbl_in                  oe_order_pub.line_tbl_type;
   l_action_request_tbl_in        oe_order_pub.request_tbl_type;
   l_header_rec_out               oe_order_pub.header_rec_type;
   l_line_tbl_out                 oe_order_pub.line_tbl_type;
   l_header_val_rec_out           oe_order_pub.header_val_rec_type;
   l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
   l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
   l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
   l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
   l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
   l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
   l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
   l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
   l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
   l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
   l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
   l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
   l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
   l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
   l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
   l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
   l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
   l_action_request_tbl_out       oe_order_pub.request_tbl_type;
   l_chr_program_unit_name        VARCHAR2 (100);
   l_chr_ret_status               VARCHAR2 (1000)                     := NULL;
   l_msg_count                    NUMBER                                 := 0;
   l_msg_data                     VARCHAR2 (2000);
   l_num_api_version              NUMBER                               := 1.0;


   CURSOR c_so_details
   IS
    SELECT oh.header_id, ol.line_id, fr.update_ssd||' '||'23:59:00' update_ssd, so_number, msi.segment1, fr.line,
       ol.line_number || '.' || ol.shipment_number,ol.inventory_item_id
FROM oe_order_headers_all oh,
       oe_order_lines_all ol,
       mtl_system_items msi,
       xx_upd_so_line_ssd fr
WHERE oh.header_id = ol.header_id
   AND ol.inventory_item_id = msi.inventory_item_id
   AND msi.organization_id = ol.ship_from_org_id
   AND fr.item = msi.segment1
   AND fr.so_number = oh.order_number
   AND fr.line = ol.line_number || '.' || ol.shipment_number
   ;

BEGIN
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = 'XX_USER';

   SELECT responsibility_id, application_id
     INTO l_resp_id, l_appl_id
     FROM fnd_responsibility_vl
    WHERE responsibility_name like 'XX - OM Sales Super User';

   fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

   FOR iso_rec IN c_so_details
   LOOP
      l_line_tbl_in (1) := oe_order_pub.g_miss_line_rec;
      l_line_tbl_in (1).line_id := iso_rec.line_id;
       l_line_tbl_in (1).schedule_ship_date := to_date(iso_rec.update_ssd,'DD-MON-RRRR HH24:MI:SS');
      l_line_tbl_in (1).operation := oe_globals.g_opr_update;
      oe_msg_pub.delete_msg;

      oe_order_pub.process_order
                   (p_api_version_number          => l_num_api_version,
                    p_init_msg_list               => fnd_api.g_false,
                    p_return_values               => fnd_api.g_false,
                    p_action_commit               => fnd_api.g_false,
                    p_line_tbl                    => l_line_tbl_in,
                    x_header_rec                  => l_header_rec_out,
                    x_header_val_rec              => l_header_val_rec_out,
                    x_header_adj_tbl              => l_header_adj_tbl_out,
                    x_header_adj_val_tbl          => l_header_adj_val_tbl_out,
                    x_header_price_att_tbl        => l_header_price_att_tbl_out,
                    x_header_adj_att_tbl          => l_header_adj_att_tbl_out,
                    x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl_out,
                    x_header_scredit_tbl          => l_header_scredit_tbl_out,
                    x_header_scredit_val_tbl      => l_header_scredit_val_tbl_out,
                    x_line_tbl                    => l_line_tbl_out,
                    x_line_val_tbl                => l_line_val_tbl_out,
                    x_line_adj_tbl                => l_line_adj_tbl_out,
                    x_line_adj_val_tbl            => l_line_adj_val_tbl_out,
                    x_line_price_att_tbl          => l_line_price_att_tbl_out,
                    x_line_adj_att_tbl            => l_line_adj_att_tbl_out,
                    x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl_out,
                    x_line_scredit_tbl            => l_line_scredit_tbl_out,
                    x_line_scredit_val_tbl        => l_line_scredit_val_tbl_out,
                    x_lot_serial_tbl              => l_lot_serial_tbl_out,
                    x_lot_serial_val_tbl          => l_lot_serial_val_tbl_out,
                    x_action_request_tbl          => l_action_request_tbl_out,
                    x_return_status               => l_chr_ret_status,
                    x_msg_count                   => l_msg_count,
                    x_msg_data                    => l_msg_data
                   );
      l_msg_data := NULL;

      IF l_chr_ret_status <> 'S'
      THEN
         FOR iindx IN 1 .. l_msg_count
         LOOP
            l_msg_data := l_msg_data || ' ' || oe_msg_pub.get (iindx);
         END LOOP;
      END IF;

      DBMS_OUTPUT.ENABLE (10000);
      DBMS_OUTPUT.put_line ('Sales Order => '||iso_rec.SO_NUMBER||' - Line Number => '|| iso_rec.line);

      DBMS_OUTPUT.put_line ('Return Status: ' || l_chr_ret_status);
      DBMS_OUTPUT.put_line ('Error Message: ' || l_msg_data);
   END LOOP;

COMMIT;

END;

Cancel Sales Order line using Process_order API

2012-06-13T12:19:00.002-07:00

-- Temp Table
create table xx_cancel_so_temp (so_number NUMBER, line_num VARCHAR(50),item_num VARCHAR2(80), org_code VARCHAR2(10))

-- Script to Cancel the SO line
DECLARE
   l_user_id                      NUMBER;
   l_resp_id                      NUMBER;
   l_appl_id                      NUMBER;
   l_header_rec_in                oe_order_pub.header_rec_type;
   l_line_tbl_in                  oe_order_pub.line_tbl_type;
   l_action_request_tbl_in        oe_order_pub.request_tbl_type;
   l_header_rec_out               oe_order_pub.header_rec_type;
   l_line_tbl_out                 oe_order_pub.line_tbl_type;
   l_header_val_rec_out           oe_order_pub.header_val_rec_type;
   l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
   l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
   l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
   l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
   l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
   l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
   l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
   l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
   l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
   l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
   l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
   l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
   l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
   l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
   l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
   l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
   l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
   l_action_request_tbl_out       oe_order_pub.request_tbl_type;
   l_chr_program_unit_name        VARCHAR2 (100);
   l_ret_status               VARCHAR2 (1000)                     := NULL;
   l_msg_count                    NUMBER                                 := 0;
   l_msg_data                     VARCHAR2 (2000);
   l_api_version              NUMBER                               := 1.0;


   CURSOR c_so_details
   IS

      SELECT a.so_number order_number, ol.line_number, ol.shipment_number, ol.header_id, ol.line_id, ol.flow_status_code
FROM xx_cancel_so_temp a,
       oe_order_headers_all oh,
       oe_order_lines_all ol,
       mtl_system_items msi,
       mtl_parameters mp,
       wsh_delivery_details wsh
WHERE a.so_number = oh.order_number
   AND a.item_num = msi.segment1
   AND a.org_code = mp.organization_code
   AND a.line_num = ol.line_number || '.' || ol.shipment_number
   AND mp.organization_id = msi.organization_id
   AND ol.header_id = oh.header_id
   AND ol.inventory_item_id = msi.inventory_item_id
   AND wsh.source_line_id = ol.line_id
   AND wsh.source_header_id = oh.header_id
   AND wsh.released_status NOT IN ('Y','C','L','I')
   AND ol.flow_status_code IN ('AWAITING_SHIPPING','ENTERED')
   AND oh.flow_status_code IN ('BOOKED', 'ENTERED')
   ;

BEGIN
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = 'XX_USER';

   SELECT responsibility_id, application_id
     INTO l_resp_id, l_appl_id
     FROM fnd_responsibility_vl
    WHERE responsibility_name like 'XX - OM Sales Super User';

   fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

   FOR i IN c_so_details
   LOOP
      l_line_tbl_in (1) := oe_order_pub.g_miss_line_rec;
      l_line_tbl_in (1).line_id := i.line_id;
      l_line_tbl_in (1).ordered_quantity := 0;
      l_line_tbl_in (1).change_reason := '1'; --Administrative Reason
      l_line_tbl_in (1).change_comments := 'Cancel Order Line with reference to ticket#000000';
      l_line_tbl_in (1).operation := oe_globals.g_opr_update;
      oe_msg_pub.delete_msg;

      oe_order_pub.process_order
                   (p_api_version_number          => l_api_version,
                    p_init_msg_list               => fnd_api.g_false,
                    p_return_values               => fnd_api.g_false,
                    p_action_commit               => fnd_api.g_false,
                    p_line_tbl                    => l_line_tbl_in,
                    x_header_rec                  => l_header_rec_out,
                    x_header_val_rec              => l_header_val_rec_out,
                    x_header_adj_tbl              => l_header_adj_tbl_out,
                    x_header_adj_val_tbl          => l_header_adj_val_tbl_out,
                    x_header_price_att_tbl        => l_header_price_att_tbl_out,
                    x_header_adj_att_tbl          => l_header_adj_att_tbl_out,
                    x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl_out,
                    x_header_scredit_tbl          => l_header_scredit_tbl_out,
                    x_header_scredit_val_tbl      => l_header_scredit_val_tbl_out,
                    x_line_tbl                    => l_line_tbl_out,
                    x_line_val_tbl                => l_line_val_tbl_out,
                    x_line_adj_tbl                => l_line_adj_tbl_out,
                    x_line_adj_val_tbl            => l_line_adj_val_tbl_out,
                    x_line_price_att_tbl          => l_line_price_att_tbl_out,
                    x_line_adj_att_tbl            => l_line_adj_att_tbl_out,
                    x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl_out,
                    x_line_scredit_tbl            => l_line_scredit_tbl_out,
                    x_line_scredit_val_tbl        => l_line_scredit_val_tbl_out,
                    x_lot_serial_tbl              => l_lot_serial_tbl_out,
                    x_lot_serial_val_tbl          => l_lot_serial_val_tbl_out,
                    x_action_request_tbl          => l_action_request_tbl_out,
                    x_return_status               => l_ret_status,
                    x_msg_count                   => l_msg_count,
                    x_msg_data                    => l_msg_data
                   );
      l_msg_data := NULL;

      IF l_ret_status <> 'S'
      THEN
         FOR iindx IN 1 .. l_msg_count
         LOOP
            l_msg_data := l_msg_data || ' ' || oe_msg_pub.get (iindx);
         END LOOP;
       DBMS_OUTPUT.put_line ( 'Sales Order => '|| i.order_number);
       DBMS_OUTPUT.put_line ( 'Line ID=> ' || i.line_id || ' Line Cancelation Failed');
       DBMS_OUTPUT.put_line ('Return Status: ' || l_ret_status);
       DBMS_OUTPUT.put_line ('Error Message: ' || l_msg_data);
     ELSE
       DBMS_OUTPUT.put_line ( 'Sales Order => '|| i.order_number);
       DBMS_OUTPUT.put_line ( 'Line ID=> ' || i.line_id || ' Line Cancelled Successfully');
       DBMS_OUTPUT.put_line ('Return Status: ' || l_ret_status);
       DBMS_OUTPUT.put_line ('Error Message: ' || l_msg_data);

      END IF;



   END LOOP;
END;

Upload Free barcode fonts to XML Publisher Report

2011-12-22T09:03:00.000-08:00

Download the free3of9x.tiff file from web and follow the steps to upload the .tiff file.

Step 1.

Goto XML Publisher Administrator responsibility à Administration

Step 2. Below is the Administration screen à Click on Font Files tab

Step 3. Click on Create Font Files Button

Step 4. Enter below values

Font Name : FRE3OF9X

File : Browse and upload the file - fre3of9x.ttf

Click on Apply

Step 5. Click on Font Mapping tab ànd Create Font mapping Set

Step 6. Enter values

Mapping Name: FR_BARCODE

Mapping Code : FR_BARCODE
Type : FO To PDF

Click on APPLY button

Step 7. Click on Create Font Mapping button

Step 7.

Font Family : FRE3OF9X

Style: Normal

Weight: Normal

Language: English

Territory : United States

Font Type : Truetype

Click on Contine Button

Step 8.

Font : FRE3OF9X (Browse and attach the font we created )

Click on Apply Button

Step 9. Click on Configuration Tab à Expand FO Processing

Font Mapping Set: FR_BARCODE (browse and attached the newly created barcode font)

Click on Save button.

In rtf template enter the font name which is created in XML Publisher.

To replace characters other then numbers, letters, spaces, hyphens, and apostrophes from data

2011-07-13T08:57:00.000-07:00

CREATE OR REPLACE FUNCTION APPS.convert_strg (p_value IN VARCHAR2)
RETURN VARCHAR2
IS
/**************************************************************
Author : Srini
Creation Date : 07-Jul-2011
Description : This will replace characters other then numbers,
letters, spaces, hyphens, and apostrophes.
**************************************************************/
l_junk VARCHAR2 (1000) := NULL;
l_val VARCHAR2 (1000) := NULL;
--
BEGIN
l_val := p_value;

IF p_value IS NOT NULL
THEN
SELECT REPLACE
(REPLACE
(REGEXP_REPLACE
(REGEXP_REPLACE (REGEXP_REPLACE (p_value,
'([[:alpha:]])',
''
),
'([[:digit:]])',
''
),
'(-)',
''
),
'''',
''
),
' ',
''
)
INTO l_junk
FROM DUAL;
END IF;

IF l_junk IS NOT NULL
THEN
SELECT REGEXP_REPLACE (p_value, '([' || l_junk || '])', '')
INTO l_val
FROM DUAL;
END IF;

RETURN l_val;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
/

select convert_strg ('qwjr42r-00-32489_&^%$#@') from dual
Result: qwjr42r-00-32489

Send mail using uuenclode and mailx/Mail

2011-06-18T04:22:00.000-07:00

To mail one file
uuencode 221153.log 221153.log | mail srini.ik@gmail.com

To mail one file Multiple Files
(
for i in /usr/tmp/*.rdf
do
uuencode $i $(basename $i)
done
) | mailx -s "Test files" srini.ik@gmail.com

Convert .ps to pdf

2011-03-25T10:48:00.000-07:00

Below command is useful to convert the post script file to pdf. =============================================================
gs -dBATCH -dSAFER -DNOPAUSE -q -sDEVICE=pdfwrite -sOutputFile=o43796813_3.pdf -c '.setpdfwrite << /PageSize [842 595] /Orientation 3 >> setpagedevice setdistillerparams' -f o43796813.out

=============================================================
You can also use ps2pdf command.
You can also adjust the page size as per requirement.

Calling Webservice from PLSQL Procedure

2011-03-22T11:04:00.000-07:00

CREATE OR REPLACE FUNCTION APPS.WS_QUOTE( symbol in varchar2) RETURN sys.xmltype
as
env VARCHAR2(32767);
http_req utl_http.req;
http_resp utl_http.resp;
resp sys.xmltype;
in_xml sys.xmltype;

url varchar2(2000):='http://www.webservicex.net/stockquote.asmx?WSDL';
BEGIN
-- generate_envelope(req, env);
env:= '

' || symbol || '

';

http_req := utl_http.begin_request(url, 'POST','HTTP/1.1');
utl_http.set_body_charset(http_req, 'UTF-8');
utl_http.set_header(http_req, 'Content-Type', 'text/xml');
utl_http.set_header(http_req, 'Content-Length', length(env));
utl_http.set_header(http_req, 'SOAPAction', 'http://www.webserviceX.NET/GetQuote');
utl_http.write_text(http_req, env);
http_resp := utl_http.get_response(http_req);
utl_http.read_text(http_resp, env);
utl_http.end_response(http_resp);
in_xml := sys.xmltype.createxml(env);
resp := xmltype.createxml(env);
dbms_output.put_line('same output');
dbms_output.put_line(SUBSTR(env, 1, 245));
dbms_output.put_line(SUBSTR(env, 246, 500));
dbms_output.put_line(SUBSTR(env, 501, 750));
dbms_output.put_line(SUBSTR(env, 751, 1000));
RETURN resp;
END;

Read XML Bank weblink and upload required ILS - USD and ILS - EUR curLS - USD and ILS - EULS - USD and ILS - EUR curLS - USD and ILS - EUR currency conversion ratesrency conversion ratesR currency conversion ratesrency conversion rates (HTTPURITYPE.createuri)

2011-02-25T04:56:00.001-08:00

CREATE OR REPLACE procedure APPS.gl_daily_conv_rate (P_ERRBUF OUT VARCHAR2, P_RETCODE OUT NUMBER) IS
   l_url                HTTPURITYPE;
   l_xml                XMLTYPE;
   webpage              clob;
   l_usd                VARCHAR2 (100) := 'ZZ';
   l_eur                VARCHAR2 (100) := 'XX';
   l_usd_rate           NUMBER          := 1;
   l_eur_rate           NUMBER          := 1;
   l_last_update        VARCHAR2 (100) := 'YY';
   l_last_update_date   DATE;
   l_err_msg            VARCHAR2 (1000);
   l_step_code          VARCHAR2 (100);
   lv_request_id        NUMBER;
   lv_user_id NUMBER:= fnd_global.user_id;
   lv_resp_id NUMBER:= fnd_global.resp_id;
   lv_resp_appl_id NUMBER:= fnd_global.RESP_APPL_ID;
   l_cnt        NUMBER:=0;
   l_success_flag   VARCHAR2(10):= 'N';
   l_wait    NUMBER:= 0;

BEGIN
   l_step_code := 'Start accessing weblink';
fnd_file.put_line(fnd_file.log,l_step_code );
l_success_flag   := 'N';
LOOP
l_cnt := l_cnt+1;
l_wait:= 0;
LOOP
l_wait:= l_wait+1;
exit when l_wait = 10;
END LOOP;
BEGIN

   l_url :=
          HTTPURITYPE.createuri ('http://www.bankisrael.gov.il/currency.xml');
   l_step_code := 'Convert to XML';
   fnd_file.put_line(fnd_file.log,l_step_code );
   webpage := l_url.getclob();
      insert into xxtemp values (sysdate, webpage);
   l_xml := l_url.getxml ();
   l_step_code := 'Get Currency Node for USD';
   fnd_file.put_line(fnd_file.log,l_step_code );
   l_success_flag:= 'Y';

EXCEPTION
WHEN OTHERS THEN
l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('Error while:' || l_step_code || ' - '
                            || l_err_msg
                           );
fnd_file.put_line(fnd_file.log,'Error while:' || l_step_code || ' - '
                            || l_err_msg );

END;

EXIT WHEN l_cnt = 3 OR l_success_flag = 'Y';

END LOOP;
   l_usd :=
      l_xml.EXTRACT ('/CURRENCIES/CURRENCY[CURRENCYCODE="USD"]/RATE').getstringval
                                                                          ();
   l_step_code := 'Get Currency Node for EUR';
   fnd_file.put_line(fnd_file.log,l_step_code );
   l_eur :=
      l_xml.EXTRACT ('/CURRENCIES/CURRENCY[CURRENCYCODE="EUR"]/RATE').getstringval
                                                                          ();
   l_step_code := 'Get Currency rate for USD';
   l_usd_rate :=
      TO_NUMBER (SUBSTR (l_usd,
                         INSTR (l_usd, '>', 1) + 1,
                         (INSTR (l_usd, '<', -1) - INSTR (l_usd, '>', 1) + 1
                         ) - 2
                        )
                );
fnd_file.put_line(fnd_file.log,l_step_code||' - '||l_usd_rate );
   l_step_code := 'Get Currency rate for EUR';
   l_eur_rate :=
      TO_NUMBER (SUBSTR (l_eur,
                         INSTR (l_eur, '>', 1) + 1,
                         (INSTR (l_eur, '<', -1) - INSTR (l_eur, '>', 1) + 1
                         ) - 2
                        )
                );
fnd_file.put_line(fnd_file.log,l_step_code||' - '||l_eur_rate );
   /*l_step_code := 'Get Last Update node';
   l_last_update := l_xml.EXTRACT ('/CURRENCIES/LAST_UPDATE').getstringval ();
   l_step_code := 'Get Last Update Date';
   l_last_update_date :=
      TO_DATE (SUBSTR (l_last_update,
                       INSTR (l_last_update, '>', 1) + 1,
                         ( INSTR (l_last_update, '<', -1)
                          - INSTR (l_last_update, '>', 1)
                          + 1
                         )
                       - 2
                      ),
               'YYYY-MM-DD'
              );*/
   l_step_code := 'Inserting data into GL_DAILY_RATES_INTERFACE';

   BEGIN
      l_step_code := 'Inserting USD data ';
fnd_file.put_line(fnd_file.log,l_step_code );
      INSERT INTO gl_daily_rates_interface
                  (from_currency, to_currency, from_conversion_date,
                   to_conversion_date, user_conversion_type,
                   conversion_rate, mode_flag
                  )
           VALUES ('USD', 'ILS', TRUNC(SYSDATE)+1,
                   TRUNC(SYSDATE)+1, 'Corporate',
                   l_usd_rate, 'I'
                  );

      l_step_code := 'Inserting EUR data ';

      INSERT INTO gl_daily_rates_interface
                  (from_currency, to_currency, from_conversion_date,
                   to_conversion_date, user_conversion_type, conversion_rate,
                   mode_flag
                  )
           VALUES ('EUR', 'ILS', TRUNC(SYSDATE)+1,
                   TRUNC(SYSDATE)+1, 'Corporate', l_eur_rate,
                   'I'
                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         l_err_msg := SQLERRM;
         DBMS_OUTPUT.put_line (   'Error while:'
                               || l_step_code
                               || ' - '
                               || l_err_msg
                              );
fnd_file.put_line(fnd_file.log,'Error while:' || l_step_code || ' - '
                            || l_err_msg );

         ROLLBACK;
    P_RETCODE:= 2;
    RAISE;
   END;

   COMMIT;

BEGIN

fnd_file.put_line(fnd_file.log,'Submit standard interface program');

fnd_global.APPS_INITIALIZE(lv_user_id, lv_resp_id,lv_resp_appl_id);

lv_request_id:= FND_REQUEST.SUBMIT_REQUEST (
               application => 'SQLGL'
              ,program      => 'GLDRICCP'
              ,description => 'Program - Daily Rates Import and Calculation'
              ,start_time   => SYSDATE
              ,sub_request => FALSE);

COMMIT;

fnd_file.put_line(fnd_file.log, 'Submitted Request :'||lv_request_id );

END;

EXCEPTION
   WHEN OTHERS
   THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('Error while:' || l_step_code || ' - '
                            || l_err_msg
                           );
fnd_file.put_line(fnd_file.log,'Error while:' || l_step_code || ' - '
                            || l_err_msg );

      ROLLBACK;
        P_RETCODE:= 2;

END;

To find the concurrent manager for a specific concurrent request.

2010-05-13T01:04:00.000-07:00

SELECT b.user_concurrent_queue_name
FROM fnd_concurrent_processes a,
fnd_concurrent_queues_vl b,
fnd_concurrent_requests c
WHERE a.concurrent_queue_id = b.concurrent_queue_id
AND a.concurrent_process_id = c.controlling_manager
AND c.request_id = :p_request_id

Using NEW_VALUE parameter In sql

2010-05-13T01:02:00.003-07:00

set feedback on
col log_mode_val new_value log_mode print
col log_mode_val1 new_value log_mode1 print

select 'xyz'||to_char(sysdate,'DDMONYYYY') log_mode_val from dual;

select 'emp'||to_char(sysdate,'DDMONYYYY') log_mode_val1 from dual;

create table &&log_mode as select * from emp;

create table &&log_mode1 as select * from dept;

Creating XML and DTD using PL/SQL

2010-05-13T01:00:00.001-07:00

-- Create Table
create table EMPXML(
NAME varchar2(30),
XML_DTD clob);

-- Create Procedure :
-- Pass your query as parameter and run the procedure
-- Query table. You can see generated XML and DTD in Table

CREATE OR REPLACE PROCEDURE P_Create_XML_DTD (P_Query IN Varchar2) as
v_ctx dbms_xmlgen.ctxHandle;
v_clob CLOB;
v_clob1 CLOB;
BEGIN
v_ctx := dbms_xmlquery.newContext(p_query);
INSERT
INTO EMPXML VALUES
(
'EMP XML',
empty_clob()
)
RETURNING xmldoc
INTO v_clob;
dbms_xmlquery.getXml
(
v_ctx, v_clob
)
;
INSERT
INTO EMPXML VALUES
(
'EMP DTD',
empty_clob()
)
RETURNING xmldoc
INTO v_clob1;
dbms_xmlquery.getdtd
(
v_ctx, v_clob1
)
;
Commit;
END;
/

Find Dependencies on Table using SQL

2010-05-13T00:59:00.001-07:00

Find Dependencies.

SELECT owner, object_type, object_name, object_id, status , decode(replace(object_type,' ','_'),'PACKAGE_BODY','PACKAGE',replace(object_type,' ','_')) type_link
FROM sys.all_objects
WHERE object_id IN (
SELECT object_id
FROM public_dependency
CONNECT BY PRIOR object_id = referenced_object_id
START WITH referenced_object_id = :OBJECT_ID )

fnd_Flex_ext.Get_ccId API USE?

2010-05-13T00:58:00.000-07:00

This API is used to insert row in GL_CODE_CONBINATIONS table.

fnd_Flex_ext.Get_ccId is a function which wil return number.

declare
l_conctenated_segment varchar2(200) := '050'||'.'||'100'||'.'||'00000'||'.'||'000'||'.'||'000';
l_ccid number:=0;
Begin
l_ccid:= fnd_flex_ext.get_ccid
('SQLGL'
,'GL#',100
, TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS')
,l_conctenated_segment) ;

END;

--------------------------

Declare
v_Conctenated_Segment varchar2(200) :='050'||'.'||'100'||'.'||'00000'||'.'||'000'||'.'||'000';
v_Chart_Of_Accounts_Id varchar2(200);
v_n_Code_Combination_Id varchar2(200);
Begin
SELECT gSob.Chart_Of_Accounts_Id -- To Get the Chart of Accounts id
INTO v_Chart_Of_Accounts_Id
FROM gl_Sets_Of_Books gSob
WHERE gSob.Set_Of_Books_Id = fnd_Profile.VALUE('GL_SET_OF_BKS_ID');

v_n_Code_Combination_Id:=
FND_FLEX_EXT.GET_CCID('SQLGL','GL#',v_Chart_Of_Accounts_Id,
To_Char(SYSDATE,'YYYY/MM/DD HH24:MI:SS'),
v_Conctenated_Segment);
IF v_n_Code_Combination_Id<>0 THEN
insert into gl_interface table.....
ELSE
update the stg table with status 'E'
End If;
End;

Copy Sales order, Book Order Sales order and Close SO Header and Line using API's

2010-05-13T00:55:00.000-07:00

CREATE OR REPLACE PACKAGE XXCP_COPY_SALES_ORDER is
PROCEDURE MAIN (P_ERRBUF OUT VARCHAR2,
P_RETCODE OUT NUMBER,
P_ORDER_NUM IN NUMBER
) ;

END;
/

CREATE OR REPLACE PACKAGE BODY XXCP_COPY_SALES_ORDER is
PROCEDURE MAIN
(P_ERRBUF OUT VARCHAR2,
P_RETCODE OUT NUMBER,
P_ORDER_NUM IN NUMBER
) is
l_query_header_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_copy_rec Oe_order_copy_util.Copy_rec_type := Oe_order_copy_util.get_copy_rec;
l_new_order_number oe_order_headers_all.order_number%TYPE;
l_error_msg_data Oe_msg_pub.MSG_TBL_TYPE;
v_error_msg VARCHAR2(4000);
l_line_id varchar2(500);
l_line_type_id number;
l_header_id number;
l_order_type_id number;
l_Line_cnt number := 0;
l_ERRMSG varchar2(200);
MULTIPLE_ORDERS EXCEPTION;
cursor cur_line(p_header_id NUMBER) is select line_id,line_type_id from oe_order_lines
where header_id = p_header_id;

BEGIN

BEGIN

select header_id,order_type_id into l_header_id,l_order_type_id from oe_order_headers
where order_number = P_ORDER_NUM;

EXCEPTION
WHEN TOO_MANY_ROWS THEN
l_ERRMSG:= 'ERROR:More Then One Order exist';
RAISE MULTIPLE_ORDERS;

WHEN OTHERS THEN
l_ERRMSG := SQLERRM;

FND_FILE.PUT_LINE(FND_FILE.LOG, l_ERRMSG);

RAISE MULTIPLE_ORDERS;

END;

for rec_line in cur_line(l_header_id) loop

l_Line_cnt := 1+l_Line_cnt;

l_line_id := to_char(rec_line.line_id)||','|| l_line_id;

l_line_type_id := rec_line.line_type_id;
end loop;

--- fnd_global.apps_initialize(0, 51124, 660);
l_copy_rec.api_version_number := 1;
l_copy_rec.init_msg_list := FND_API.G_TRUE;
l_copy_rec.COMMIT :=FND_API.G_TRUE;
l_copy_rec.Copy_Order := FND_API.G_TRUE;
l_copy_rec.hdr_count := 1;
l_copy_rec.hdr_list :=l_header_id;
l_copy_rec.append_to_header_id:= NULL;
l_copy_rec.hdr_info := 'T';
l_copy_rec.hdr_type := 1997;
l_copy_rec.hdr_descflex := 'T';
l_copy_rec.hdr_credit_card_details := 'T';
l_copy_rec.hdr_scredits := 'T';
l_copy_rec.hdr_attchmnts := 'T';
l_copy_rec.hdr_holds := 'T';
l_copy_rec.manual_order_number:= P_ORDER_NUM;
l_copy_rec.all_lines := 'T';
l_copy_rec.line_count := l_Line_cnt;
l_copy_rec.line_list := l_line_id;--l_line_list;
l_copy_rec.line_type := 1996;
l_copy_rec.incl_cancelled := 'T';
l_copy_rec.line_price_mode := 1;--Original Price;
--l_copy_rec.line_price_date := NULL;
-- l_copy_rec.line_discount_id := NULL;
l_copy_rec.line_descflex := 'T';
l_copy_rec.line_scredits := 'T';
l_copy_rec.line_attchmnts := 'T';
--l_copy_rec.return_reason_code := v_return_reason_code;
l_copy_rec.default_null_values:= 'T';
Oe_order_copy_util.copy_order( p_copy_rec => l_copy_rec
,x_header_id => l_query_header_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);

IF l_return_status = 'S' THEN --Copy successful

update oe_order_lines_all set source_type_code = 'INTERNAL' where header_id = l_query_header_id;

OE_ORDER_BOOK_UTIL.Complete_Book_Eligible
(p_api_version_number=> 1
,p_init_msg_list=> FND_API.G_TRUE
,p_header_id => l_query_header_id
,x_return_status=> l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Order Header ID:'|| l_query_header_id);

ELSE

Oe_msg_pub.GET_MSG_TBL(l_error_msg_data);

FOR i IN l_error_msg_data.FIRST .. l_error_msg_data.LAST LOOP

FND_FILE.PUT_LINE(FND_FILE.LOG,l_error_msg_data(i).message);

END LOOP;

END IF;

IF l_return_status = 'S' THEN

for rec_line in cur_line(l_query_header_id) loop

OE_ORDER_CLOSE_UTIL.Close_Line
( p_api_version_number => 1.0
, p_line_id => rec_line.line_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'CLOSE LINE:'||rec_line.line_id||'-'|| l_return_status);

END LOOP;

oe_order_close_util.close_order(1, NULL, l_query_header_id, l_return_status, l_msg_count, l_msg_data);

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'CLOSE HEADER:'||l_query_header_id||'-'|| l_return_status);

END IF;

COMMIT;
EXCEPTION
WHEN MULTIPLE_ORDERS THEN

FND_FILE.PUT_LINE(FND_FILE.LOG, l_ERRMSG);

WHEN OTHERS THEN
l_ERRMSG := l_ERRMSG||'-'||l_msg_data;
ROLLBACK;
FND_FILE.PUT_LINE(FND_FILE.LOG, l_ERRMSG);

END MAIN;

END XXCP_COPY_SALES_ORDER;
/

Oracle 10g With Clause

2010-05-13T00:53:00.001-07:00

WITH val AS ( SELECT SUBSTR(:val||',', INSTR(:val, ',', 1, rn)+1 ,
INSTR(:val||',', ',', 1, rn)+1 -
INSTR(:val||',', ',', 1, rn) )col
FROM (
SELECT ROWNUM rn FROM dual
CONNECT BY LEVEL<=LENGTH(:val) - LENGTH(REPLACE(:val,',',''))+1))
SELECT col
FROM val;

ROWS TO COLUMN

2010-05-13T00:51:00.000-07:00

declare
TYPE c_refcur IS REF CURSOR;
l_str VARCHAR2(4000);
l_col VARCHAR2(4000);
c_ref c_refcur;
l_num number;
l_query varchar2(4000) :='select column_name from all_tab_columns where table_name like ''MTL_SYSTEM_ITEMS_B'' and rownum < 6 order by 1 desc' ;
l_dlmtr varchar2(10) := ',';
BEGIN

OPEN c_ref FOR l_query;
LOOP
FETCH c_ref INTO l_col;

l_str := l_str || l_dlmtr || l_col;

EXIT WHEN c_ref%NOTFOUND;

END LOOP;
CLOSE c_ref;
dbms_output.put_line( 'select '||SUBSTR(l_str,2)|| ' from MTL_SYSTEM_ITEMS_B');
EXCEPTION
WHEN OTHERS THEN
l_str := SQLERRM;
IF c_ref%ISOPEN THEN
CLOSE c_ref;
END IF;

END;
/

OUTPUT: select SECONDARY_UOM_CODE,EAM_ACT_SHUTDOWN_STATUS,EAM_ACT_NOTIFICATION_FLAG,DUAL_UOM_DEVIATION_HIGH,DUAL_UOM_CONTROL,DUAL_UOM_CONTROL from MTL_SYSTEM_ITEMS_B